SSIS package fails in sql server agent, but not in visual studio. ODBC Connection

Jooooosh picture Jooooosh · Nov 6, 2014 · Viewed 15.5k times · Source

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.

Answer

Henno picture Henno · Jul 2, 2015

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.