none
Assigning Passwords to Connection Managers using Expressions RRS feed

  • Question

  • Hi  Experts here,

         Sorry if this query had been raised earlier.  While  In DTS Packages we retrieve  connection details for all Connections in a Package from a table and then assign them to the following global Variables

    gv_Source_User, gv_Source_Pwd, gv_Source_DataSource, gv_Source_InitialCatalog

     

    Finally we reassign these variables  to repective Connection Properties using Dynamic Properties Task. After Migrating to SSIS though we are able to assign almost all variables to Properties of Connection Managers via Expression except the Password which we donot find in the drop down list in order to assign gv_Source_Pwd.

     

    Is there any work around to assign passwords dynamically?

     

    Many Thanks

    Subhash Subramanyam

     

    Wednesday, May 2, 2007 3:58 AM

Answers

  • No, you can't use script.  You can assemble the complete connection string (with password) in an expression-based variable and then assign that to the ConnectionString property through an expression on the connection manager.

    See www.connectionstrings.com if you need help building the string.
    Wednesday, May 2, 2007 1:35 PM

All replies

  • Hi, Was also thinking if we can go about using script task to do the same ..

     

      

    Wednesday, May 2, 2007 10:18 AM
  • No, you can't use script.  You can assemble the complete connection string (with password) in an expression-based variable and then assign that to the ConnectionString property through an expression on the connection manager.

    See www.connectionstrings.com if you need help building the string.
    Wednesday, May 2, 2007 1:35 PM
  • Many Thanks to JayH.

    I already had this in my mind yesterday with a bit of  confusion  - How to work out the code which uses Different connection Managers (what if it was Oracle). i.e. the Connection string built will be of different format .

    A question for you is: Though the security can be compromised from Value set to ConnectionString, Why Password property hasn't been added in the dropdown list?

     

    e.g. SQL OLEDB Connection,  Data Source=Serverxyz;User ID=sa;Initial Catalog=PUBS_CATALOG;Provider=SQLOLEDB;Persist Security Info=True;

    For Oracle, First Manually provide all the details, so that ConnectionString will be built on Connection Manager Properties. This seems really a burden that we must manually verify every derived connectionstring if it can successfully connect.

     

    Many Thanks once again

    Subhash Subramanyam

    Wednesday, May 2, 2007 1:45 PM