Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

Using Excel 2007 in SSIS 2005

Many companies are not in a rush to upgrade their SQL Servers because of the enormous cost to upgrade.  This results in the majority of companies still running previous versions of SQL Server (2005, 2000, and even earlier).  Many times as the developer you are forced to work with older server components but new file sources like Excel 2007 with SQL Server and SSIS 2005.  In this case, there are some workarounds that will allow using what seem like two incompatible platforms.  This is a highly blogged about topic (including on this site SQLServerCentral) but with some recent questions about it I thought I'd throw one more in the mix. 

Before following these instructions ensure that you have the most up to date service packs installed to have to correct data provider for this example. 

Create a new OLE DB Connection Manager and select Microsoft Office 12.0 Access Database Engine OLE DB Provider from the Provider list.  Then change the Server or file name to the Excel 2007 workbook file path.

 

Select the All page and change the Extended Properties to Excel 12.0.  Then back to the Connection page and hit Test Connection to verify the setup worked.

 

Now you can use an OLE DB Source in your Data Flow to connect to any sheet in that Excel workbook.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.