Wednesday, December 12, 2007

Walkthrough Connecting to Excel 2007 with SSIS

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 *
FROM `Sheet1$`
To Query a range of cells (A2:C10)
SELECT *
FROM `Sheet1$A2:C10`
Well that's it, happy codeing.

Best,
Tyler










10 comments:

Anonymous said...

Thank you. Your article saved me from a lot of googling. :-)

Anonymous said...

Thanks for the post! Big Time Saver...I have benn trying to import Excel 2007 files into SQL Server 2005 and kept getting strange error messages. Your walkthrough worked perfectly!

Thanks again,

a fellow developer

Anonymous said...

Hi, Thank you for this insight. can u also explain me how to store more than 255 char length data intoa excel sheet. I am getting error

Uzwa Ul Haque said...

thanks buddy

dan.ling@pcubed.com said...

How do I connect to my Excel 2007 document if it is storred in a SharePoint 2007 Doc Lib?

Jeremy said...

TO: dan.ling@pcubed.com

On Step 8: Convert your SharePoint file location URL to a shared file location.

For example:
https://sharepoint_server/sites2/your_home_site/report%20folder/your_excel_file.xlsx

Change it to:
\\sharepoint_server\sites2\your_home_site\report%20folder\your_excel_file.xlsx

To validate, copy the changed link and go to 'Start'-->'Run' and paste the changed link. If you run that and your excel document opens then put that link in the 'Data Source' field.

If not, check to make sure the link is exactly like your SharePoint address.

Note: To get the direct URL to a particular file, hover on the file name link, right click, choose 'Properties', and copy everything in the 'Address URL' (remember to highlight everything, some of the text is hidden on the next few lines). Also %20 represents a space, so keep it in your path name.

Hope this helps.

Kara said...

I created a blank excel sheet that I'm trying to use as my destination. It says I only have one destination column, and what I'm trying to input is more like 20-25 columns. Is there a way for it to automatically create those columns?

digital signature Adobe Reader said...

Very helpful article ! I was always curious about all these complex algorithms that are being used in these ssl encryptions.

Anonymous said...

Thank you!

Rick Campbell said...

Article is so old but is still very helpful. Wonder if you have the same valuable tips about online data room ? I'm gethering info on this topic at the moment.