error with ssis package in sql server job

2
votes

Help solve the problem. There is a ssis package which in visual studio runs without problems, but returns an error in the sql task. The packet in the work takes the data from sql server and writes in mysql. The error is this:

It is executed on behalf of the user: DOMAIN \ system. Microsoft (R) SQL Server Version 10.50.6000.34 for 64-bit (C) Microsoft Windows (Microsoft Corporation), 2010 package execution program. All rights reserved. Start: 19:53:23 Error: 2019-06-22 19: 53: 24.10 Code: 0xC0208452 Source: Data Flow Task ADO NET Destination [1281] Description: The destination ADO NET could not establish the connection {5BC4C8B7-B5DD-45EF-AEF5 -66D296B28305}. The connection may be damaged. End of error Error: 2019-06-22 19: 53: 24.10 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: Error while checking the components "ADO NET Destination" (1281), error code 0xC0208452. End of error Error: 2019-06-22 19: 53: 24.10 Code: 0xC004700C Source: SSIS.Pipeline Data Flow Task Description: Error checking one or more components. End of error Error: 2019-06-22 19: 53: 24.10 Code: 0xC0024107 Source: Data Flow Task Description: Errors while checking the task. End of error DTExec: The DTSER_FAILURE (1) package has completed. Start: 19:53:23 Done: 19:53:24 Passed: 0.421 seconds. The package could not be completed. Step completed with an error.

Thank you in advance !

3
mysqlsql-serverssisssis-2008sql-agent-job
Educated guess: VS run in 32-bit mode and SQL Server SSIS tries to use 64-bit mode and the source/target db is unreachable. Please try setting 32-bit mode on SSISDB/SQL Server Agent job. – Lukasz Szozda
Yes, installation use 32 bit environment is worth – noanother

3 Answers

0
votes

Possible error causes:

  1. 32/64 bit conflict: Make sure that the mode you are running with is relevant to the MySQL Driver installed. Try running the package in 32-bit mode in SQL job:

  2. Connection credentials: If you are using the current user credentials to connect to MySQL then make sure that the SQL service user is granted for establishing the connection.

0
votes

When you chose SSIS Package type as a step of a job, there is a 32-bit runtime check box in the Step>General>Configuration, check that box and then re-run the package through job.I think it's better that you deploy your package and run it as sql server agent service account through ssis catalog. I hope this will solve the problem.

0
votes

Everything turned out to be simpler, it was necessary to add a proxy account, and it all worked. Thank you all for your help and for responding!