0

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 :

enter image description here

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".
Improve this question
6
  • Make sure ODBC drive for MySQL is installed on the database sevrer where you are running the job.
    – Rajesh Ranjan
    Mar 8, 2022 at 15:32
  • It is installed ! but only the 32bits version, and I also made sure the '32-bit runtime' is checked on Job Properties.
    – Hamza Hamdani
    Mar 8, 2022 at 15:39
  • Are you getting any error?
    – Rajesh Ranjan
    Mar 8, 2022 at 15:40
  • The one in the picture above
    – Hamza Hamdani
    Mar 8, 2022 at 15:41
  • it's not visible
    – Rajesh Ranjan
    Mar 8, 2022 at 15:42

2 Answers 2

Reset to default
0

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.

enter image description here

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.

enter image description here

Improve this answer
1
  • Yes I made sure of all that ! but SQL Server Agent can't execute the job, manually it completes all the tasks...
    – Hamza Hamdani
    Mar 9, 2022 at 8:02
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.