OLE DB Source to extract data from MS Office Excel 2007 and Access DB in SSIS

To load data from a data source that uses Microsoft Office Excel 2007, use an OLE DB source. We cannot use an Excel source to load data from an Excel 2007 data source.

To load data from a data source that uses Microsoft Office Excel 2003 or earlier, use an Excel source.

---

The data provider that the connection uses depends on the version of the Excel file format:

• For Excel 2003 or earlier format, the Excel connection manager, uses the Microsoft Jet OLE DB Provider.

• For Excel 2007 or later format, the package requires the OLE DB provider for the Microsoft Office 12.0 Access Database Engine.

On a 64-bit computer, we must run packages that connect to Microsoft Excel data sources in 32-bit mode. Both the Microsoft Jet OLE DB Provider and the OLE DB provider for the Microsoft Office 12.0 Access Database Engine are only available in 32-bit versions.

Connect to an Access Database
Requires an OLE DB connection manager and a data provider.

The data provider depends on the version of Access that created the data source:

• For Access 2003 and earlier, the package requires the Microsoft Jet OLE DB Provider.

• For Access 2007, the package requires the OLE DB provider for the Microsoft Office 12.0 Access Database Engine.

On a 64-bit computer, we must run packages that connect to Microsoft Access data sources in 32-bit mode. Both the Microsoft Jet OLE DB Provider and the OLE DB provider for the Microsoft Office 12.0 Access Database Engine are only available in 32-bit versions.

 

Please feel free to correct me by commenting your suggestions and feedback.