Why does my ODBC connection fail when running an SSIS load in Visual Studio but not when running the same package using Execute Package Utility
I'm working on a Data Mart loading package in SSIS 2012. When attempting to execute the package in Visual Studio I get this error:
"The AcquireConnection method call to the connection manager Data Warehouse.ssusr failed with error code 0xC0014009".
When I test the connectivity of the Connection Manager Data Warehouse.ssusr I see that it passes.
When I execute the package outside of Visual Studio using the Execute Package Utility, the package runs.
I don't understand what's going on.
The package also refuses to run using the SQL Server Job Schedule, if that has anything to do with anything.
Answer
Making some assumptions here, but I'm going to assume that this is a 32 vs 64 bit issue. To verify, try these two commands from a command prompt (Windows Key, R, cmd.exe or Start, Run, cmd.exe)
"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx
"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx
The first will run your package in 32 bit mode whilst the second runs it in 64 bit mode. This is going to matter as your drivers and any DSNs you've created are going to only be visible in the 32/64 bit world.
Fixing SSDT
Once you've identified which one you need, probably 32 bit version, you'd need to ensure your project is using the appropriate run-time. Right click on your project and select Properties and then navigate to the Debugging tab under the Configuration Properties.
After inverting the Run64BitRuntime value, I assume your package will work from within SSDT.
Fixing SQL Agent
You will need to edit the existing SQL Agent job to change the bittedness of the job step. This will be under the Configuration tab and then under the Advanced tab. Check/Uncheck the 32-bit runtime.
Lies and deception
Observant folks may see that the dtexec offers a /X86 option. Don't believe it. The only way to get the correct bit-ness is to explicitly call the correct dtexec.exe The documentation even says as much but nobody reads documentation.
This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt.