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)
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".