All we need is an easy explanation of the problem, so here it is.
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 :
NOTE : Am using 32bits ODBC driver for MySQL.
Any ideas ?
UPDATE (had to update because image not visible to everyone)
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".
How to solve :
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:
- Review if your MySql Server has the same ODBC Driver Version as the
SQL Server where SSIS resides.
- Review if the DSN Connection matches the Connection name in your
- 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.
- Go to Visual Studio, open your project, and check your SSIS Project
properties –> Debugging — Run64BitRunTime is False.
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.
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂