SSIS mysql to mssql

  • 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?

  • Hi

    Did you set Run64BitRuntime as false?

    Br.

    Mike

  • 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]

  • 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

  • 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.

  • 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

  • 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

  • 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?

  • 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].

  • 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

  • I created in

    C:\Windows\SysWOW64\odbcad32.exe

    and

    I instaled mysql-connector-odbc-5.1.12-win32

  • 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

  • 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.

  • 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

  • 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

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

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