SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35894 Visits: 16699
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
sneumersky
sneumersky
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3256 Visits: 487
https://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=15
prakashr.r7
prakashr.r7
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 197
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...
prakashr.r7
prakashr.r7
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 197
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?
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35894 Visits: 16699
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
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7433 Visits: 2033
.when we do config file at the master package environment, will the file pass the value to the child pacakges


You wont need to create a config file for the master package, only the child packages. The master package should have variables, these will hold the values that you want to pass to the child packages. In the child packages, create variables there as well to catch the values that the parent package variables will pass to the child packages. Right click in the control flow area and select 'package configuration..' , then Add, then follow the wizard. The configuration type will be 'parent package variable.' This is where you map the values between the variables of the child and parent packages (make certain data types match). With this complete in your child package you are free to use your local variables in expressions (for ole db connection 'servername' property for example) as you normally do.

----------------------------------------------------
How to post forum questions to get the best help
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35894 Visits: 16699
mmartin1 (7/31/2013)
You wont need to create a config file for the master package, only the child packages.

Whilst that's true in the strictest sense, all but the simplest of packages should use config file (or some other external method of package configuration). That way, when you want to run the package on a different server or change the number of retries, for example, you just edit the configuration file instead of having to redeploy the whole solution.

John
prakashr.r7
prakashr.r7
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 197
Okay Martin...Let me try with config file for each child package...
prakashr.r7
prakashr.r7
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 197

Whilst that's true in the strictest sense, all but the simplest of packages should use config file (or some other external method of package configuration). That way, when you want to run the package on a different server or change the number of retries, for example, you just edit the configuration file instead of having to redeploy the whole solution.


I believe i got the idea of what you are saying. Let me try. Sorry that I took some time to make myslef clear.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search