I have an SSIS package that runs fine in visual studio 2010, but as a job it sql server it fails when connecting to an ODBC database.
The project encrypts sensitive data with a password. This is because the proxy server isn't my account. It also happens to be the user that connects to the ODBC with a connection string. The job connects to a couple non-ODBC databases successfully before failing at the ODBC connection.
The project is set up to run in 32-bit mode, but strangely enough when I set it to 64-bit it'll fail at the ODBC connection.
All the servers are set to run with the current user except for the ODBC database, which has a connection string. Could this have something to do with it?
Here's the error message
Description: An ODBC error -1 has occurred. End Error Error: 2014-11-07 09:05:14.18 Code: 0xC0014009
Description: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. End Error
Description: The AcquireConnection method call to the connection manager MyODBC failed with error code 0xC0014009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error Error: 2014-11-07 09:05:14.18 Code: 0xC0047017 Source: MyDFT SSIS.Pipeline
Description: ODBC Source failed validation and returned error code 0x80004005. End Error
Error: 2014-11-07 09:05:14.18 Code: 0xC004700C Source: MyDFT SSIS.Pipeline
Description: One or more component failed validation. End Error
Error: 2014-11-07 09:05:14.19 Code: 0xC0024107 Source: MyDFT
Description: There were errors during task validation. End Error
DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:05:00 AM Finished: 9:05:14 AM Elapsed: 14.071 seconds.
The package execution failed. The step failed.
Thanks in advance.
I was able to fix this problem by creating a 64 bit System DSN with the same name as the 32 bit one.
Task Manager's Platform column shows that my Visual Studio (devenv.exe) is 32 bit and that my SQL Server Agent service (SQLAGENT.EXE) is 64 bit. That explains it.