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

Question

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.

Newland

Packages that don't run when they're scheduled are usually a result of permissions problems (because the job executes as the SQL Agent account) and/or environment issues (components not installed, drive mappings not available). Could any of those things apply here? Are you using the same user account in both VS and dtexecui? – Roaster

Answer 1

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.

Commuter

What if I both commands work properly but the task still fails after deployment on the same server? – Gassman

@rezCash mine is that case, have you find any solution in this case . . – Ula

@user2463026 my fault was altering an old connection (which was no longer valid), instead of creating a new connection and change the modules with old connection to the new one. I think connections will no change after each deploy if they have same name. – Gassman

ensure the server you deployed to has the same drivers as your dev machine. That Ace driver is a real pain. – Teammate

Answer 2

I first tried to go update an old connection manager connection string, due to the server name being updated.

Don't do this.

I created a new connection manager with the updated connection string and redeployed my solution after changing the connection manager source for any modules/tasks relying on the old connection manager.

Billposter