Error in SSIS connecting to MySQL
-
I have
1.Server_A hosting
MySQL (5.5.29-0ubuntu0.12.04.2-log)
2.Server_B (windows2012
MySQL (5.6.38-log)
SQL Server 2012On my workstation using Visual studio 2015 I created SSIS with target server version SQL2012
SSIS has
Step 1 connect to SQL 2012 on Server_B ,get some values
Step 2 transfer data from MySQL on Server_A to SQL 2012 on Server_BConnection to SQL server build with expression
Connection to MySQL build in expression
"server="+ @[User::Host_MySql] +";user id="+ @[User::UserId_MySql] +";database="+ @[User::DB_MySql] +";port="+ @[User::Port_MySql] +";password="+ @[User::Password_MySql]+";"
Variable store default values
Package saved with option "do not save sensitive"
Package run successfully from Visual studio on my workstationWhen I copy package to to Server_B and execute like
dtexec /FILE "\"D:\SSIS\Package_01.dtsx\"" /CHECKPOINTING OFF /REPORTING EWCDI > D:\SSIS\log\log_20180509.txtI get errors
Source: Data Flow Task mysql_001 [31]
Description: ADO NET Source has failed to acquire the connection {some value...} with the following error message: "Could not create a managed connection manager.".My workstation and and Server_B both have mySQL ODBC 5.3 ANSI and Unicode Driver installed
I can successfully can create and test odbc connection to Server_A from Server_BI could not find reason for error "Could not create a managed connection manager"
Thank you
Alex -
Need more details. What does your connection manager look like for the connection to Server A ? With an ODBC connection, you usually need an ADO.Net source or an ODBC Source.
Not sure what else to ask until there's a clearer picture as to how the package is put together.Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy) -
When "Edit it" inside package
I see that is listed as .\Net providers\MySQL Data providerIn properties of connection
Connection manager type = ADO.NET:MySql.Data.MySqlClient.MySqlConnection, MySql.Data, Version=6.9.8.0, Culture=neutral, PublicKeyToken=somevalue...Expression
ConnectionString= "server="+ @[User::Host_MySql] +";user id="+ @[User::UserId_MySql] +";database="+ @[User::DB_MySql] +";port="+ @[User::Port_MySql] +";password="+ @[User::Password_MySql]+";" - ebooklub - Wednesday, May 9, 2018 10:30 AM
Can you successfully test that connection from within the connection manager? If you can, try to create a separate package just to test getting data from Server B into a test table on Server A.
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy) -
Thanks for help I will put a issue on hold for for some time
I tried to create new ssis that use mysql as data source and pass connection string as expression from,previous SSIS , but my expression at RUN time somehow concatenate server name and user and trowing connection error
I will do more investigation , but I have supposition that even I specified target server as SQL 2017 in my project created in Visual studio 2015. my SSIS is not properly converted -
Package saved with option "do not save sensitive"
I wonder if you changed this to maybe require a password to open / run the package , if this would make a difference. It may be not saving the password switch in the connection string.
----------------------------------------------------
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply