I have a SSIS package developed in Visual studio 2019, but then as the server has got only version 2016, so Changed my package to 2016 and deployed. I am using ODBC source to connect to MYSQL database server and OLEDB Destination to SQL Server database.
The System DSN version is MYSQL ODBC 5.3 ANSI Driver (both in my laptop and Server, also the DSN name is same )
I have set Run64bitRuntime to false.
Also tried executing via cmd prompt "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\Filename.dtsx - It worked fine.
I have also tried Ping to the MYSQL server IP - it is pingable
When executing the package i get the below error,
Data Flow Task:Error: ODBC Source failed validation and returned error code 0x80004005. Data Flow Task:Error: The AcquireConnection method call to the connection manager failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Package:Error: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. Package:Error: An ODBC error -1 has occurred.
Can someone please help? I am struggling with this issue for about a week now.
My thoughts on this - permissions. I am wondering: does your ODBC connection have the username/password hard-coded in or are you getting it from the calling application (ie SQL Server)? My guess is it gets the credentials from the calling application and the SQL Server Agent service credential doesn't have permission on the MySQL database side.
User contributions licensed under CC BY-SA 3.0