USING BI Studio how to create Dynamic Connection String in SSIS Package


  • Hi


     I need help for Connection string:


    Requirement: When we create SSIS Pacakge using Businessinteligence studio.Each  Source and Destination or whatever we using the Control  required DB Connection.

    we connect theDB server and Database Table through manaully .Instead of Manual i need dynamic Global varible for Connection String .How to achieve this connection string.

    because suppose we create SSIS Package in Developement  Server Latter We change the Server from Developement to Another  Testing Server .  at that time we dont requierd for changing manulay.any one pls reply me.


    Same as  in Dotnet we give configiration XML file .we gave the Connection strng. how to in SSIS we do?


    Thanks & Regards






    Monday, June 19, 2006 5:12 AM

All replies

  • Right click the main control flow and select Configurations menu, follow the wizard to select configuration of you choice (registry, XML file, environment, SQL server) and the properties to configure.
    Monday, June 19, 2006 6:06 AM
  • if you would like to use your defined connections in your package dynamically

    - define for each connection parameter which should be dynamic (e.g. servername, login, pw) a variable which scope is valid for the package (or part of the package)

    - define the connection string as you would do it normally (means fill in everything you need to connect. This is used during validation)


    - define in the expressions all those parameters which are dynamically filled by the variables e.g. Servername, InitialCatalog


    - use the TRIM function ,e.g. Servername should be defined as TRIM( @User::Servername] )


    Maybe this helps you a little bit




    Monday, June 19, 2006 1:16 PM
  • That was pretty general.  Can you more specific?  This seems to be a hard task for many to complete, so the more specific you can be, the better.


    Friday, August 25, 2006 4:50 PM
  • hi michael,

    I have created a xml configuration file using Package Configuration.

    but here i have a small problem, how do i give connections using the generated file for the oledb source and destination

    thanks in advance



    Saturday, January 20, 2007 11:32 AM
  • Create 1 OLEDB connection manager

    Create 4 variables (oledbservername, oledbinitialcatalog, oledbusername, oledbpassword)

    Give those variables a value (manually or from a config file)

    Create 1 script task. Put these variables as readonly input

    Write the following code inside the task:


    Dim conns As Connections = Dts.Connections

    Dim cmOleDb As ConnectionManager = conns("OleDB Connection")

    cmOleDb.Properties("ServerName").SetValue(cmOleDb, Dts.Variables("OleDBServerName").Value.ToString())

    cmOleDb.Properties("InitialCatalog").SetValue(cmOleDb, Dts.Variables("OleDBInitialCatalog").Value.ToString())

    cmOleDb.Properties("UserName").SetValue(cmOleDb, Dts.Variables("OleDBUser_Name").Value.ToString())

    cmOleDb.Properties("Password").SetValue(cmOleDb, Dts.Variables("OleDBPass_word").Value.ToString())

    Wednesday, February 28, 2007 11:45 AM
  • How to give values to the  variables (oledbservername, oledbinitialcatalog, oledbusername, oledbpassword) from config file.


    Friday, April 13, 2007 5:18 PM
  • Friday, April 13, 2007 11:43 PM
  • I got this to work and tested my connection from within the script and it worked just fine.   However, when I try to use the connection in the next task (an Execute SQL Task), I get an error saying that the connection was not made.      Any ideas?   Do I need to do anything special when I create the OLE DB Connection Manager that I'm using?





    P.S.  I'm a newbie with this...


    Thursday, August 16, 2007 2:40 PM