How to change the connection string of child packages in Execute Package task?

  • Hi Friends,

    I am tryting to execute multiple packages so i am using Execute Package Task to run the packages in certain order. But my problem is that these packages have to execute in different environment so i have to change the connection strings for all the packages. Since i am using one master package to execute, i could not find a way to change the connection strings in DTEXECUI..... How to use this master package to run in different environment?

    Could you help me friends? Any suggestions would be appreciated...

  • Did you take a look at package configurations?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (7/11/2013)


    Did you take a look at package configurations?

    Koen, configuring for each packages? If yes, do we have to use that in master package execution...? Just curious, will that work?

  • Normally I create package configurations for each package.

    Is there something special about your set-up that prevents this?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (7/11/2013)


    Normally I create package configurations for each package.

    Is there something special about your set-up that prevents this?

    Sorry Koen, nothing special... so we have to add all the package configurations and run with the master package ...? I was thinking that how the master EXECUI would accept these config files..? That's why i came here for clarification.

  • One configuration option is to use a parent package variable in each of the packages that is called from your master package.

    John

  • prakashr.r7 (7/11/2013)


    Koen Verbeeck (7/11/2013)


    Normally I create package configurations for each package.

    Is there something special about your set-up that prevents this?

    Sorry Koen, nothing special... so we have to add all the package configurations and run with the master package ...? I was thinking that how the master EXECUI would accept these config files..? That's why i came here for clarification.

    DTEXECUI is the graphical version of DTEXEc, you cannot use this in a package. You can call however DTEXEC to execute a specific package and specify a configuration file as a parameter. Maybe that is the direction that you want to pursue?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (7/11/2013)


    prakashr.r7 (7/11/2013)


    Koen Verbeeck (7/11/2013)


    Normally I create package configurations for each package.

    Is there something special about your set-up that prevents this?

    Sorry Koen, nothing special... so we have to add all the package configurations and run with the master package ...? I was thinking that how the master EXECUI would accept these config files..? That's why i came here for clarification.

    DTEXECUI is the graphical version of DTEXEc, you cannot use this in a package. You can call however DTEXEC to execute a specific package and specify a configuration file as a parameter. Maybe that is the direction that you want to pursue?

    Okay, But i am not clear with config files.... What i understand from you is to create config file for each child package and use that while executing the master package...Is that correct? What i am wondering is do we have to use all the config files? do we have any option to use just one config file to execute all the child packages through master package?

    Koen, if this is a simple task , I am sorry for asking questions repeatedly ...

  • John Mitchell-245523 (7/11/2013)


    One configuration option is to use a parent package variable in each of the packages that is called from your master package.

    John

    Sorry John, I am not getting you...Could you please make me clear?

  • You can create only one config file that stores for example the servername and the database. And you can pass this config file along when calling the child packages with DTEXEC.

    In this case you'll have to make a config file for each environment.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • prakashr.r7 (7/12/2013)


    Sorry John, I am not getting you...Could you please make me clear?

    Check this out. You can then use the variable to set the ConnectionString property of your connection manger in the child package.

    John

  • Koen Verbeeck (7/12/2013)


    You can create only one config file that stores for example the servername and the database. And you can pass this config file along when calling the child packages with DTEXEC.

    In this case you'll have to make a config file for each environment.

    Do we have to create config file while creating the master package? I am just curious ....if we have 5 child packages ...all those combined together forming a master package...when we do config file at the master package environment, will the file pass the value to the child pacakges? how does one package's variable affect the other?

    I am getting confused on this...Please bear with me, Koen...

  • John Mitchell-245523 (7/12/2013)


    prakashr.r7 (7/12/2013)


    Sorry John, I am not getting you...Could you please make me clear?

    Check this out. You can then use the variable to set the ConnectionString property of your connection manger in the child package.

    John

    Okay John..How do we change the connection string for the child packages.... we create config files for each package....when i use Execute Package task to create a master package , i don't see any way (i may not know) to change the connection string even running through BIDS....so if we create config file, how does this file will affect all the child package connection string? Why i am asking this because, child packages are created by different project...i create new project for master package...so how does these two packages connect with each other through config files?

  • The config file does not (directly) affect the connection string in the child package. The child package inherits the value of a variable from the master package. You then use that variable to build an expression to set the value of the connection string in your child package. Have a read of this for more information. Once you've created your packages like that, the only way you can set the value of that variable is to call the child package from the parent, therefore if you're running it in BIDS you need to run the parent package and wait for it to call the child.

    John

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

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