The Excel Source and Connection Manager – The basics
In this post I will be reviewing the Excel Source and Excel Connection Manager. The sample package and files can be found here for 2008 and here for 2005 and guidelines on use are here.
Fig 1: The SSIS Excel Source
How do you read data from an Excel Workbook in SSIS?
The answer to that is, it depends on the version of Excel . If it is 2003 or earlier, you can use the the Excel Connection Manager and Excel Source. If it is 2007 or later, you use a specially configured OLE DB Connection Manager, as described in the MSDN article How to: Connect to an Excel Workbook. An example of this is in the sample package (Data Flow 4). Though the sample package for this post includes an example of each version of Excel, but I will only be discussing the Excel source for 2003 and earlier from here on.
Configuring the Excel Connection Manager
The Excel connection manager is pretty simple to set up – all it requires is the file path, Excel version, and to know whether it has Column Names in the first row.
Fig 2: The Excel Connection Manager
If you look at the Properties of the Connection Manager once it is set up, you can see a Password field – this is misleading – you cannot connect to a password protected workbook. So, if you have to connect to a secure workbook you need to look at either other means of extracting that data or alternative security for the workbook.
Configuring the Excel Source
The Excel Source is very similar to the OLE DB source. This can initially be confusing as in the Data Access Mode drop-down it talks in terms of Tables, Views and SQL Commands. When it says Table or View, what it means in Excel speak is Sheets and Named Ranges. When it talks in terms of SQL – it really means it. You can construct SQL statements to pull restricted amounts of, or modified versions of the spreadsheet data. An example is below:
Fig 3: SQL in the Excel Source
Just remember to qualify the Sheet / Range Name with square brackets – e.g. [Sheet1$]- if hand writing code. Examples of each type of access is available in Data Flows 1 – 3 in the sample package.
Summary
Use the Excel Source and Excel Connection manager when reading from workbooks from Excel 2003 and prior. Be aware the driver behind it can behave unexpectedly at times, and it is worth paying attention to the “Usage Considerations” section of the MSDN documentation if you are having unexpected results.
Documentation for the Excel Source can be found here for 2008 and here for 2005. Similarly Documentation for the Excel Connection Manager can be found here for 2008 and here for 2005.