3

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.

Jooooosh
  • 331
  • 2
  • 4
  • 13
  • Please post the error message and if possible an image of your package design interface. – Bryan Nov 06 '14 at 23:18
  • Connect to the machine you are running it on. Run it from the command line. Does it work there? Is this SSIS 2012 and if so, are you using the project deployment model? – billinkc Nov 07 '14 at 02:26
  • Added the error message. Running it in the command line with all the arguments in the SQL Server Agent step gives me the same error. The project's in 2010, and I unfortunately don't have access to 2012. Running the package in 64-bit in visual studio gives me the same error. Could the Agent be running it in 64-bit, or is it something unrelated? – Jooooosh Nov 07 '14 at 14:11
  • 4
    Check if the SQL Agent job step that runs this package is set to run it in 32 bit mode. – Raj More Nov 07 '14 at 14:30
  • Thanks that fixed it. So much time spent on a simple solution. That's development for you. – Jooooosh Nov 07 '14 at 14:59
  • Where did you specify whether the step is set to run in 32 or 64 bit mode? – Henno Jul 02 '15 at 17:48
  • Actually, I got the agent to successfully execute the job when I just added a 64 bit System DSN with exactly the same name and content as I had for 32 bit one. – Henno Jul 02 '15 at 17:54
  • 1
    You can also go into job properties -> steps -> edit -> execution options -> use 32 bit runtime. – Jooooosh Jul 03 '15 at 15:28

1 Answers1

4

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.

Henno
  • 1,448
  • 4
  • 18
  • 30