SSIS package does not run when called from a SQL Server Agent job step

0
votes

I need to do Data Migration task from MySQL database to SQL Server Database 2012. I have created SSIS package for doing it. I have installed ODBC drivers of version 5.2.7 for both 32 bit and 64 bit. I have created DSN for connecting with MySQL database. This SSIS package is running successfully using Visual Studio.I have deployed SSIS package to Integration Service Catalogs and created SQL Server Agent job to run this package.But I am getting following error while running SQL Server Agent job:

"Acquireconnection method call to the connection manager failed with error code 0xc0014009"

"odbc source failed validation and returned error code 0x80004005."

I am connecting MySQL database and SQL Server using VM. I have tried to run this SSIS package with 32 bit runtime marked and changed Run64BitRuntime property to False. But still I am facing the same error.

Please note that, there is no odbc driver for MySQL is installed on the Server where SQL Server is residing but on the VM, both 32bit and 64 bit MySQL ODBC drivers are installed.

Is this the cause of mentioned error?

2
odbcssis-2012

2 Answers

0
votes

Please check whether you are using User DSN or system DSN in the odbc driver configuration. When deploying your packages user DSN may not work. Try creating a System DSN. That should work. I have faced the same issue. Please check my notes.

https://jyothishbhaskaran.wordpress.com/2017/09/07/how-to-create-and-use-data-source-for-mysql-using-odbc-driver/

0
votes

Within the SQLAgent Job's TASK settings, I had to go into the "Advanced" tab and set it to run with the 32-bit system.