0

I have a parent pkg that calls a few child pkgs. For each child pkg I have a sql agent job that will override some Conenctions values as in dtexec, where you can use the handy /Conn[ection] to make the pkg configuring in a different way simply pointing to a diff SQL SSIS Conf table (common pattern). The problem is that Execute Package Task (called by the parent) does not have any option rather than calling the child pkg itself (I cannot call Execute Package Task passing smth like /Conn[ection] as I can do with dtexec) so a natural coice would be using Execute Process Task to call dtexec on Child pkg with a appropriate /Conn[ection] setup. Based on your experience are there any drawbacks/issues to consider when using Execute Process Task DTEXEC rather than the Execute Package Task or they are the same thing at the end...? Mario

rio
  • 685
  • 9
  • 16
  • 1
    You can configure the package to have its connections set by package parameters. The `Execute Package` task allows you to set these package parameters making your connection configurable. – TsSkTo Nov 21 '13 at 10:55

2 Answers2

3

The way that I've seen this handled is to create a variable to hold the folder path and variables to hold the package names for each child package. These should be added to the Parent Package.

enter image description here

Then in your connection manager for the child package, you can write an expression to set the connection string dynamically. Right click and select properties for the connection in connection manager, then concatenate these two variables.

Expression Code: 

@[User::sPkgFolder]+ @[User::sPkgFilename]

In addition you can set up a XML configuration file and set the variables via the XML file so that as you move the packages from environment to environment you don't have to manually change the code base, but only need to change the value of the path in your configuration file. You can also set up as many variables as need to hold all your child packages.

swilliams
  • 532
  • 3
  • 11
  • Hi @SWilliams, thanks I am pushing to use the xml indirect configuration as this would avoid changing the code at all. – rio Nov 26 '13 at 16:22
0

As already exaplined by SWilliams you can send parent package variables down to a child package using the execute package task, then in the child pacakge you can use this variable in an expression to set the connection. This is far less convoluted than using SQL Agent, configurations etc. and has the added bonus that if you run it in parallel it won't get confused.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91