SQL Server Agent not running SSIS package

by

The question:

I have followed many articles to create an SSIS pipeline package, deploy it and schedule it.
I’m trying to transfer data from two remote servers, an OLEDB driver that connects to a SQL Server Database as a Source and an OLEDB provider for ODBC drivers that connects to a MySQL Database as a Destination…

The Pipeline works very well on Visual Studio and when I deploy it and execute it from SSMS, but when I create a Job I get the following error :

SQL Server Agent not running SSIS package

NOTE : Am using 32bits ODBC driver for MySQL.

Any ideas ?

UPDATE (had to update because image not visible to everyone)

ERROR :

Data Flow Task:Error: OLE DB Destination failed 
validation and returned error code 0xC020801C.

Data Flow Task:Error: SSIS Error Code 
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNE
CTIONMANAGER. The AcquireConnection method call to 
the connection manager 
"remotemysql.com.uVt96HeM3y.uVt96HeM3y1" failed with 
error code 0xC0202009. There may be error messages 
posted before this with more information on why the 
AcquireConnection method call failed.

Package1:Error: SSIS Error Code 
DTS_E_OLEDBERROR. An OLE DB error has occurred. 
Error code: 0x80004005.
An OLE DB record is available. Source: "MSDataShape" 
Hresult: 0x80004005 Description: "Data provider could not 
be initialized.".
An OLE DB record is available. Source: "Microsoft OLE 
DB Provider for ODBC Drivers" Hresult: 0x80004005 
Description: "[Microsoft][ODBC Driver Manager] Data 
source name not found and no default driver specified".

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

I think the error is related to the ODBC configuration on your server or computer.

I’m wondering if you are using an SSIS Catalog to execute your package, or are you using DTExec on a server to execute your package?

Would be a good idea if you can share more details about the server?

I’m assuming you are using an SSIS Catalog.

I would recommend reviewing the following settings:

  1. Review if your MySql Server has the same ODBC Driver Version as the
    SQL Server where SSIS resides.
  2. Review if the DSN Connection matches the Connection name in your
    SSIS Package.
  3. Go to ODBC Connection manager in the SQL Server where
    the SSIS Catalog resides, and make sure the ODBC Driver version
    matches the version in your SSIS Package.
  4. Go to Visual Studio, open your project, and check your SSIS Project
    properties –> Debugging — Run64BitRunTime is False.

SQL Server Agent not running SSIS package

Because you explained you are using odbc32, then your package project will be Run64BitRunTime “false” as I described above.
If you made this change, then rebuild your project and deploy it again.

SQL Server Agent not running SSIS package

Method 2

It worked. It seems that setting an OLE DB Destination was the problem. I replaced it with an ODBC Destination and the agent did its a Job


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment