Devart Forums

Posted: **Thu 29 Mar 2012 20:12**

Hi all,
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.

In the example, ACAD_PROGRAMS is the source table in the MSSQL, and DATATEL_ACAD_PROGRAMS is the destination table in the MySQL database.

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!

Posted: **Mon 02 Apr 2012 15:16**

dbresson wrote:Description: ADO NET Destination has failed to acquire the connection {C98180FF-AA76-4449-BB72-8AA70F31D5F3}. The connection may have been corrupted.

This is not a provider-specific exception. So we cannot identify the reason of the problem exactly. Please try the following:
1. Run dotConnect for MySQL installation package in your deployment environment (where you are running your SQL Server Agent) in the ["Minimal" + "SQL Server Business Intelligence Solutions"] mode. This will solve two possible issues:
- registering provider in the DbProviderFactories section of machine.config
- technical licensing (there is no way to add a technical license into SSIS project)
2. Make sure that your SQL Server Agent has permissions to run the package.
3. Check that your MySQL server is set to accept connections from another hosts and add the corresponding exception for MySQL's port on your firewall (or turn of firewalls for testing purpose).

Posted: **Tue 17 Apr 2012 15:37**

Thanks for the reply! I'd very much like to get this problem figured out...on to my response:

1. I can't find anywhere in the GUI install for dotConnect for MySQL Free edition where I can select this install option. How do I go about doing this?

2. Yes, the package and job were both created by the user that owns the database, and I even switched the user that runs the job in SQL Server Agent to this same user in one of my troubleshooting steps, but it did not help either.

3. There is no firewall nor SELinux setup on the RHEL5 box hosting MySQL. There are no hosts.allow or hosts.deny -type settings either. IPTables is not running. I also thought that if this were the case that there was a firewall or port problem then the debug from BIDS would not work either, which is not the case here. The user in MySQL being used here for the import is set to be able to connect from any host also.

Thanks,

Dave

Posted: **Thu 19 Apr 2012 09:57**

dbresson wrote:1. I can't find anywhere in the GUI install for dotConnect for MySQL Free edition where I can select this install option. How do I go about doing this?

Please run the installation package of dotConnect for MySQL Professional Edition (in the ["Minimal" + "SQL Server Business Intelligence Solutions"] mode).

Posted: **Thu 19 Apr 2012 21:33**

Ok, I installed the Pro version with the Minimal + SQL Server Business Intelligence Solutions mode, re-built my package in BIDS and re-deployed it. The Debug in BIDS worked, however after redeploying the package and trying the job again in SQL Server Agent, I still get the same error:

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:  4:27:09 PM  Error: 2012-04-19 16:27:11.86     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-04-19 16:27:11.88     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-04-19 16:27:11.88     Code: 0xC004700C     Source: ACAD_PROGRAMS SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2012-04-19 16:27:11.88     Code: 0xC0024107     Source: ACAD_PROGRAMS      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:27:09 PM  Finished: 4:27:11 PM  Elapsed:  2.515 seconds.  The package execution failed.  The step failed.

Any suggestions?

Thanks,

Dave

Posted: **Tue 24 Apr 2012 12:35**

1.

dbresson wrote:I installed the Pro version with the Minimal + SQL Server Business Intelligence Solutions mode, re-built my package in BIDS and re-deployed it.

Have you installed dotConnect for MySQL Pro (Minimal + SQL Server Business Intelligence Solutions) in your deployment environment?
2.

dbresson wrote:The Debug in BIDS worked, however after redeploying the package and trying the job again in SQL Server Agent, I still get the same error

Please try using another provider to establish connection to MySQL (e.g., MySQL Connector/ODBC). Does it work without any error?
http://social.msdn.microsoft.com/Forums ... ba5bd4509/

Posted: **Tue 24 Apr 2012 16:20**

1. The development and deployment are both done on the production server - they are one and the same.

2. We do have MySQL Connector/ODBC installed on the server and setup in Data Sources (ODBC) on the System DSN - testing the connection there to the same MySQL database in question works just fine, no errors.

Thank you so much for looking into this! Let me know if you need me to try anything else!

Dave

Posted: **Thu 03 May 2012 16:56**

Sorry, but it works in our environment: SSIS package with data migration SQL Server -> MySQL via SQL Server Agent job. Have you set the lower_case_table_names = 0 setting in the [mysqld] section of the my.ini file of your MySQL Server (to resolve possible problems with upper/lower case)?

Posted: **Fri 04 May 2012 17:53**

Our MySQL database is hosted on a RHEL5 box, and lower_case_table_names is set to 0 by default (as I understand it). We do not change this variable to a different value via the config.

I'm not sure why this would cause the package to work in BIDS Debug mode, but not in SQL Server Agent, however.

Thank you for your help - any additional suggestions would be very much appreciated!

Thanks,

Dave

Posted: **Wed 16 May 2012 14:15**

Ok - we finally found a solution for this. We had to put the password parameter (and value) into the Connection String for the Data Source in question (the MySQL database that is being exported to) in the properties of the job step for the SQL Server Agent job. After doing that, the job all of a sudden works, and it can connect to the MySQL database and insert the records there. Once you've done this, the password is no longer displayed in that same Connection String, but it still works. I guess maybe that's a security feature that it doesn't keep displaying that parameter or something.

Thanks for your help,

Dave

Devart Forums

trouble connecting MySQL in SSIS Package job SQL Srvr Agent

trouble connecting MySQL in SSIS Package job SQL Srvr Agent

Re: trouble connecting MySQL in SSIS Package job SQL Srvr Ag

Re: trouble connecting MySQL in SSIS Package job SQL Srvr Ag

Re: trouble connecting MySQL in SSIS Package job SQL Srvr Ag