Import Excel files with SQL Server Integration Services on a 64-bit server
Today I deployed some SSIS packages to a test environment. These packages needed to import some excel files to a certain database. Testing the packages resulted in the following error:
Error 0xc0202009: {F1B3B35C-FAE3-48F6-A169-4E4D8D99F9B6}: An OLE DB error has occurred. Error code: 0×80004005.
An OLE DB record is available. Source: “Microsoft JET Database Engine” Hresult: 0×80004005 Description: “Unspecified error”.
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager “DestinationConnectionExcel” failed with error code 0xC0202009.
It seems there just isn’t an 64-bit version of the JET OleDb driver for Excel and because the test server was a 64-bit, this caused the Excel-import to fail.
- To make the package work using BI-studio you need to change your project debugging properties: Set Run64BitRuntime to False
- To make the package work when started from a SQL Server Agent Job, you need to execute the package using the 32-bit version of DTExec.
To do this create a Operating System Job Step and type the command to execute the package using DTExec in the 32-bit folder: C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\
The complete commmand will be something like this:
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe /DTS “\MSDB\Import” /SERVER “.” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING V
Good luck!
