Excel 2007 as Data Source in Integration Services 2005

Most of us know how to use MS-Excel 2003 file format in an SQL Server 2005 Integration Services. But we could not use MS-Excel 2007 file format in an SQL Server 2005 Integration Services. The Connection manager for Excel files option that we would usually choose would not work.

This was sorted out with SQL Server 2005 SP2.

Here I have explained the procedure, how to make MS-Excel 2007 file format as a data source in SQL Server 2005 Integration Services.

Procedure

1. Open the Business Intelligence Developer Studio and start a new Integration Services Project.
2. Drag a Data Flow task on to the designer surface and double click on it.
3. Right click in the “Connection Manager” pane and select “New OLE DB Connection“.

4. Click ‘New‘ to create a new data connection.

5. After created the connection, click ‘Next‘ to select the appropriate provider.

6. Under the Provider drop-down, select “Microsoft Office 12.0 Access Database Engine OLE DB Provider“.

7. Click on the ‘All‘ tab of the tab strip located on the left, and type the following against the Extended Properties property “Excel 12.0“.

8. Go back to the “Connections” tab and type in the full file path of the MS-Excel 2007 file.

9. That’s it, click “Test Connection“. That’s it. We successfully created MS-Excel 2007 as data source in SQL Server 2005 Integration Services.