SSIS mysql to mssql

Question

Post reply

SSIS mysql to mssql

ddebski

Mr or Mrs. 500

Points: 557
More actions
September 18, 2013 at 9:14 am

#278028

I make same package ssis in visual studio 2010.
I load data from mysql to mssql 2012.
I created ADO.NET source(connect with mysql) and OLE DB (connect with mssql).
When I started debugging in visual studio date load from mysql to mssql without problems.
After I loaded ssis in mssql and make job in sql agnet.
When I start job I have "error ADO NET Source has failed to acquire to the connection with the following error message "Could not create a managed connection manager".
What should I do?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 1

michal.lisinski

SSCertifiable

Points: 6933

September 18, 2013 at 11:45 am

#1651221

Hi

Did you set Run64BitRuntime as false?

Br.

Mike

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

First thing that comes to mind is that when you were debugging the package, you were running it from your computer. Then you tried running it from the server.

Does the server have the required driver?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

michal.lisinski (9/18/2013)
Hi
Did you set Run64BitRuntime as false?
Br.
Mike

This is a design time setting only and doesn't affect the execution of a package on the server.

However, you might be on the right track: it's possible the server is 64-bit and hasn't the 64-bit providers installed, something Alvin was also guessing at.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ddebski Mr or Mrs. 500 Points: 557 More actions · Answer 4

Ok I see problem.

First I ran package on my computer and I didn't problem.

When I ran on other server I had problem.

I thint than problem is with ODBC on server but I don't find mistake.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

Is the ODBC 32 or 64-bit?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 6

Hi Koen

I've already met with that problem. Package tested on wkst with 32bit odbc and moved to 64bit environment, of course there were installed 32-bit odbc on the server. It started in 64bit mode first but unfortunately without success.

I'm not sure if that's it, but Imho it's a good thing to check first.

Br.

Mike

ddebski Mr or Mrs. 500 Points: 557 More actions · Answer 7

I tested on win7 64 and after i try run on win 2008 64. On win7 is ok but on win 2008 is problem.

On win 7 I have mysql odbc 5.1 driver. On win 2008 I have mysql odbc 5.2 ansi driver. In win 2008 I create DSN user and I tryed connect with datebase and I didn't problem. Why doesn't package run?

ddebski Mr or Mrs. 500 Points: 557 More actions · Answer 8

I changed in ADO NET provider from mysql data provider on odbc data provider.

Next I created DSN user and I ran test. Connect test DSN user the end succes.

I ran package on visual studio and succes but when I try run on mmsm I have error [im002].

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 9

Hi

Did you configure new DSN from C:\Windows\System32\odbcad32.exe or C:\Windows\SysWow64\odbcad32.exe?

And could you please answer Koen's question.

Regards

Mike

ddebski Mr or Mrs. 500 Points: 557 More actions · Answer 10

I created in

C:\Windows\SysWOW64\odbcad32.exe

and

I instaled mysql-connector-odbc-5.1.12-win32

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 11

Hi

It looks you have 32-bit odbc drivers , try to run your package from cmd using :

<drive>:\ Program Files(x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe /F <your package>

and let us know the status.

Br.

Mike

ddebski Mr or Mrs. 500 Points: 557 More actions · Answer 12

Sorry I have 64

I ran in cmp and I have error.

Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Nieprawidlowy klucz do uzycia w podanym stanie.". You may not be authorized to access this information. This error occurs when there is a cryptograph

Verify that the correct key is available.

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 13

Hi

I'm guessing that your native SQL connections are 'trusted'

,so please set Protection Level for this package to "DontSaveSensitive" and try again run in 32-bit mode:

<drive>:\ Program Files(x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe /F <your package>

and then in 64-bit mode:

<drive>:\ Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe /F <your package>

Br.

Mike

ddebski Mr or Mrs. 500 Points: 557 More actions · Answer 14

Problem is the end.

I change ADO NET -> MySQL Data provider to ADO NET ->Odbc Data Provider and I don't used user DSN but I connect direct use connect string "Driver={MySQL ODBC 5.1 Driver};server=<myServer>;uid=<user>;port=3306;database=<NameDataBase>;user=<user>;option=3"

Thank you for help