I'll be straight with you, I'm an SSIS newb. I've got the book, a couple of packages under my belt and that's it. The other day I had the distinct pleasure of having to connect to an Office 12 (read 2007) Excel file as a data source.
Needless to say I was pretty disappointed to find out that the Excel Connection Manager was not up to the task. The Excel Connection Manager data source can only handle files from 2003 and earlier. As an alternative you need to download and then use the Microsoft Office 12.0 Access Database Engine OLE DB Provider to get at Office 12 files.
While this introduces a deployment dependency, rest assured that you do not need to install Office 2007 to run this provider, you only need the provider to be installed on the machine that's running Integration Services.
So without further ado here's how you use the provider to access an Excel 2007 data source from the Business Intelligence Studio.
1) Install the Office 12.0 Access Database Engine OLE DB Provider (see above).
2) Open the Business Intelligence Studio and start a new Integration Services Project.
3) Drag a Data Flow task on to the designer surface and double click on it.
4) Drag an OLE DB Data source onto the designer surface and double click on it.
5) Click 'New' to create a new Connection.
6) From the Connection Manager choose Microsoft Office 12.0 Access Database Engine OLE DB Provider.
7) Click on the "Data Links" button.
8) Enter in the location of the file in the "Data Source" field.
8) Under the "Advanced" tab select the permissions you want on the file.
9) Under the "All" tab click "Edit Value" for "Extended Properties" enter in the text "Excel 12.0;HDR=YES"
10) That's it, click "Test Connection" and find out how you did.
It's also of note that you can not only query sheets as if they were tables but you can also query cell ranges. The syntax to do this looks like the following:
To Query a Worksheet (Sheet1):
SELECT *To Query a range of cells (A2:C10)
SELECT *Well that's it, happy codeing.