Most of us know how to make use of an Office Excel file in an SSIS package. As long as you use an Excel file with the .xls extension (i.e. Office 3.0, 4.0, 5.0 or Office 97 – 2003 format) you’d be fine. But, when it comes to Office Excel 2007 you’d want to do it differently. The Connection manager for Excel files option that you would usually choose would not work.
Since Office Excel 2007 and its .xlsx extension came long after SQL Server 2005 and since this new format is entirely different from the previous formats we had a little problem.
This was sorted out with Microsoft releasing the infamous Service Pack 2 (infamous ‘cos it had some other issues). Anyway this Service Pack gave us a new driver which could be used for Office Excel 2007 (a.k.a. Excel 12.0) files.
I have briefed below the steps used for creating a connection to an Excel 12.0 file.
In a new or existing package,
- Add a New Connection and choose the appropriate connection manager type (ADO.NET or OLEDB) by right-clicking on the Connection Managers section.
- This will pop up the appropriate Configuration Connection Manager box.
- Click on New… and under the Provider drop-down, select Microsoft Office 12.0 Access Database Engine OLE DB Provider (yes, it is made to be used with Excel files as well).
- 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”.
- Go back to the Connections tab and type in the full file path of the Excel 2007 file.
- And you’re done! Remember; when you use the connection manager within a Data Flow task, use an OLEDB Source or Destination instead of an Excel Source or Destination.
Data Maul 