I cannot access my Excel sheets in SSIS. The Excel version in the Connection Manager is Microsoft Excel 2007 (xlsx).
 |
| Could not retrieve the table information for the connection manager 'Excel Connection Manager'. Failed to connect to the source using the connection manager 'Excel Connection Manager' |
Solution
XLSX files don't use the out-of-the-box Microsoft.Jet.OLEDB provider, but they need the Microsoft.ACE.OLEDB provider. You either did not install it or you installed the 64bit version.
Download and install the 32bit version of the Microsoft Access Database Engine 2010 Redistributable. Because Visual Studio (SSDT/BIDS) is 32bit you can't use the 64bit provider for developing SSIS packages. If you already installed the 64bit version then you first need to remove it. You can't install 32bit and 64bit parts of office on the same machine. You will get an error when you run the installer (and you will get the same error if you have a 64bit version of Microsoft Office installed on your development machine):
 |
| You cannot install the 32-bit version of Microsoft Access Database Engine 2010 because you currently have 64-bit Office products installed. If you want to install 32-bit Microsoft Access Database Engine 2010, your first need to remove the 64-bit installation of office products. |
However this means that you can't run packages with Excel Connection Mangers in 64bit on your development machine. You need to switch to 32bit, otherwise you will get an error like:
| Information: 0x4004300A at DFT - xlsx source, SSIS.Pipeline: Validation phase is beginning. Error: 0xC0209303 at xlsxSource, Connection manager "Excel Connection Manager": The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". Error: 0xC001002B at xlsxSource, Connection manager "Excel Connection Manager": The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816 Error: 0xC020801C at DFT - xlsx source, EX_SRC - My XLSX Source [8]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error: 0xC0047017 at DFT - xlsx source, SSIS.Pipeline: EX_SRC - My XLSX Source failed validation and returned error code 0xC020801C. Error: 0xC004700C at DFT - xlsx source, SSIS.Pipeline: One or more component failed validation. Error: 0xC0024107 at DFT - xlsx source: There were errors during task validation. |
But since there is a 64bit driver... you could install it on your test/acceptance/production server and run the packages in 64bit (as long as you don't use Visual Studio on those servers).