Switch package from 64bit to 32bit
Case My Data Flow Task with an Excel connection doesn't run. It throws the following error:
Just adding some text for an example.
Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C. Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS 2008 error example (gives poor hints)
Error: 0xC0209303 at GetDataFromExcel, Connection manager "Excel Connection Manager": The requested OLE DB provider Microsoft.Jet.OLEDB.4.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: 0xC020801C at Data Flow Task, Excel 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 Data Flow Task, SSIS.Pipeline: Excel Source failed validation and returned error code 0xC020801C. Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS 2012 error example (gives better hints)
1. Solution If you have a 64bit machine, with connections that only support 32bit (like Excel, Access and old ODBC connections) you will get an error like above. The solution is to run your package in 32bit mode. This can be done within Visual Studio for debugging or within SQL Server Management Studio for scheduled packages.
This solution will also allow you to debug a Script Task, because that also doesn't work in 64bit mode.
BIDS / Visual Studio Running in 32bit mode is a Project Property. Setting it will affect all packages within the project. Right Click the SSIS project and select Properties in the context menu. Go to the Debugging pane and select false under Run64bitRuntime.
SSIS 2008
SSIS 2012
SQL Server Management Studio Edit your job and then edit the right jobstep. Go to the Execution Options pane and check "Use 32 bit runtime". This property will only effect the package (+child packages) called in this jobstep.
SSMS - Use 32 bit runtime
32 bit problems Besides running 64 bit problems there are also 32 bit problems. For example, Fuzzy Lookup will not run in 32 bit. It opens SQLDUMPER.EXE in a command line / dos box but with out errors. The solution for that is to run in 64 bit mode. So running an Excel source and a Fuzzy Lookup within the same package could be a bit of a challenge.
Note: For 32bit execution via dtexec.exe you need to pick the right folder: 32bit => C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe 64bit => C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
Note 2: if you use 32bit then the process can use less memory!