locked
Dynamic connection strings in SSIS package RRS feed

  • Question

  • Hi,

        I am new to SSIS. I have requirement like below:

    credentials required for connection string are placed in table ConfigInfo on one server containing columns-server name, Database name, username and password 
    so when i
    execute the package for eg if there are 4 server names in the table ConfigInfo, all 4 servers needs to be connected dynamically without stopping the execution.

    CREATE TABLE ConfigInfo
    (
    	ServerName VARCHAR(100),
    	DataBaseName VARCHAR(100),
    	UserName VARCHAR(50),
    	Pwd VARCHAR(50)
    )
    GO
    INSERT INTO ConfigInfo('10.6.16.197','SNNew','sa','sh1234')
    GO

         I tried but I am unable to create like that. Can any body help me out by giving an example on this. How can i create a package to take configuration values from SQL Server Table.

    Thanks,

    Vijay


    • Edited by dathy Tuesday, July 24, 2012 10:17 AM
    Tuesday, July 24, 2012 10:16 AM

Answers

All replies

  • first of all, to access the tables you will need to access the database for which you must pass the userid and password to ssis so that it can read those values from the stored table. so at least one connectionstring will be hard coded. Later you can read that table, fetch connection string data in SSIS, store them in variables and use those variables as connection string input.

    http://sql-bi-dev.blogspot.se/2010/07/dynamic-database-connection-using-ssis.html

    OR

    you can store the configuration information of the package to some sql table/ XML files where you can store the connection string values. In those config tables, you can directly update the connection string values after reading from the ConfigInfo.table. 

    http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

    regards

    joon

    Tuesday, July 24, 2012 10:27 AM
  • What if I want to use a project Connection?I can not give value to parameters in "For Each Loop Container" in section "Mapping Variable"
    Saturday, December 29, 2018 1:40 PM
  • What if I want to use a project Connection?I can not give value to parameters in "For Each Loop Container" in section "Mapping Variable"

    Create a variable inside SSIS package and set an expression for it to point to project connection  in the expression builder.

    Then you can use this variable inside your For Each loop for mapping and getting the value from it.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, December 30, 2018 7:15 AM