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.

t2
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.

t2
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:

t2
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.

11 thoughts on “The Excel Source and Connection Manager – The basics

  1. One question.. ‘m not able to find ‘NamedRangeSample’ sheet on Excel Sheet, then how come it is availabe under the drop down ‘Name of Excel Sheet’? :(

    I’m new to SSIS ( even IT industry 😛 ).. so please forgive me for stupid question :(

    Thanks in advance :)

  2. Hmm.. now, what’s ‘hidden sheet’?

    Ok, lemme give you exact data what I am seeing in the Package under ‘Table or View':
    There are four fields availabe in the drop down:
    MonkeyData$
    NamedRangeSample <– This is what 'm concerned 'bout.. :( Please notice there is no $ sign at the last..
    NamedRangeSheet$
    Sheet3#

    But when I open the Excel File, 'm good to see below three sheets:
    MonkeyData$
    NamedRangeSheet$
    Sheet3#

    It might be that 'NamedRangeSample' is not a sheet, but something else.. btw it's replicating data of 'NamedRangeSheet$'.

    nd really loads of Thanks for your time :)

  3. Thanks for the response..

    I checked the link and searched for ‘Hidden Sheet’, but the ‘Unhide Sheet’ option was greyed out which means no sheet has been hidden..
    I’m using MS Word 2007, hope it might helps. Sorry to mention earlier..

    My concern is WHY the ‘$’ sign is not at the last of ‘NamedRangeSample’.. it seems that it’s NOT a sheet, but might be a view or something :(

    MonkeyData$
    NamedRangeSample <-– Please notice there is no $ sign at the last..
    NamedRangeSheet$
    Sheet3$

    Thanks,
    Pawan

  4. Hi
    I would like to know if it is possible to open a password protected excel file using SSIS. If yes how is it done?

  5. Hi,

    Thank you for the information .
    Im able to open a password protected excel file using powershell but is there anyway i could open SSIS package with powershell so that i can dump the data from the opened excel file into a database? i want to eventually open an encrypted excel file and read the data from it and dump it into the database..

    I have noticed that once the encrypted file is open on your desktop the SSIS package can read it. I opened the encrypted file using powershell.. Is there anyway i can integrate these two to make it one whole continuous process?
    Information on this would be really helpful..

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>