Dynamically connecting to MySQL using SSIS

  • Hi All,

    I have a problem that I can't quite seem to figure out.  I want to be able to collect metadata from numerours mysql instances hosted on Amazon Web Services RDS Platform.  The AWS bit is most likely irrelevant though.

    The problem I am having is the connection getting the password at run time for the mysql user account.

    I have tried using both the ODBC connection and mysql .Net Connector and whilst I can get connected to an instance I define in the package at design time, when the package runs it will need to update the connection properties with the target mysql instance endpoint, port, username and password.  Each of these components is variables in the package.

    So far I have tried the following.
    1. Created a new ODBC connection manager, set the connection string as an expression , connection test is fine, but at execution time the password in the the user variable is not accepted when executing the sql task associated with the connection manger.  All other elements of the connection string are correct such as the endpoint, port and user.
    2 Created a new ADO.NET connection manager using the mysql.net connector, same idea, connection string set at run time from the values stored in the variables, same problem happens as when connecting via ODBC.
    I have tried setting the various package encryption options of do not save sendsitive data, Encrypt with password, Encrypt with user key etc and none work.

    Now I know I could predefine the connections as system DSN's, but I am talking over 50 servers I need to connect to which all get a monthly password change for the account being used, that in itself is a huge overhead on admin.

    Has anyone achived this before without going down the route of DSN's or using script tasks etc to connect using the mysql command line\python\powerhshell.

    MCITP SQL 2005, MCSA SQL 2012

  • Never mind,  I found the issue,  turns out with option 2 I was on the right track, although I had a typo in the connection string

    MCITP SQL 2005, MCSA SQL 2012

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

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