I have a problem I've been working on for awhile that's been driving me crazy - I have the feeling I"m missing something simple, so let's hope that's the case. I have a SSIS package I built in BIDS (MSSQL 2008) and using Devart dotConnect for MySQL for the ADO.NET connection to the MySQL destination (data is flowing from a MSSQL2008 table to a MySQL destination table).
The package works great when running and debugging in BIDS - however once I deploy it and try to use it in a SQL Server Agent job it bombs out, something about a problem connecting to the MySQL destination:
Code: Select all
Executed as user: KC\datatel. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 1:32:37 PM Error: 2012-03-19 13:32:43.31 Code: 0xC0208452 Source: ACAD_PROGRAMS DATATEL_ACAD_PROGRAMS Import [134] Description: ADO NET Destination has failed to acquire the connection {C98180FF-AA76-4449-BB72-8AA70F31D5F3}. The connection may have been corrupted. End Error Error: 2012-03-19 13:32:43.32 Code: 0xC0047017 Source: ACAD_PROGRAMS SSIS.Pipeline Description: component "DATATEL_ACAD_PROGRAMS Import" (134) failed validation and returned error code 0xC0208452. End Error Error: 2012-03-19 13:32:43.32 Code: 0xC004700C Source: ACAD_PROGRAMS SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-03-19 13:32:43.32 Code: 0xC0024107 Source: ACAD_PROGRAMS Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:32:37 PM Finished: 1:32:43 PM Elapsed: 6.141 seconds. The package execution failed. The step failed.
Any ideas? I've tried just about everything I can think of to get this thing to connect - proxy user for the agent job, making sure the Devart assemblies are in the global assembly cache, passwording the package, etc. Nothing seems to work. I've checked the forums here but haven't found anything yet. Any help or suggestions would be much appreciated.
Also, let me know if you need more information about the problem as well.
Thanks!