Hi Very simply: I call my "Master Package" from the command line / agent, and pass in the configuration file. The Master Package then calls a child package. It needs to tell the child package to use the same configuration file. Is this possible & how? Thanks Sean

Hi Sean, No. Since the command line options are not passed from a parent package to a child package it is not possible.Arthur My Blog

Thanks Arthur. What if I pass it in as a variable too - is there a way to then call the child package and specify the configuration file to use from the variable? Are there any other options? I can't use the environment variable approach, as I call the Master Package with multiple different configuration files.

Hello, If you are doing this because your config file is different on your development server than on your production server, then consider using Package configuration type 'Indirect XML configuration file'. This way you are using a system environment variable that holds the pathname to your conifg file. The Environment varibale is used on your development and production server with different pathnames. Your master and child package should both use the Indirect XML configuration type and will thus obtain the same config file. Jan D'Hondt - Database and .NET development

No chance. You may do this: use a database table for your config. Then you just pass the proper con string.Arthur My Blog

Hi Jan No - it's not an environment issue - it's because I re-use the same Master Package for several different dataloads (sources). Each one has it's own configuration file, and the first Child package is completely switched in/out to use a package specifically built for that dataload/source system. Works fine in DTS - with the Dynamic Properties Task & the package variables. Thanks Sean

Then do I need a database for each load, just for storing the settings?

The database is needed to store the config values, I do not know why designed the package this way, but I would do this: sharing the same config file among packages (yes, you can share it between a parent and children).Arthur My Blog

I still don't see how a database table will help - you can pass the connection string to the parent package, but not from there to subsequent child packages using the Execute Package Task. As another option - I suppose I could run the child packages from either a script task, or from a batch file, or even possibly from an Execute SQL Task. I expect that each of those would effectively let me set the filename of the package AND pass in the configuration filename value. Is that possible? Which of those three would be the best option?

I still don't see how a database table will help - you can pass the connection string to the parent package, but not from there to subsequent child packages using the Execute Package Task. As another option - I suppose I could run the child packages from either a script task, or from a batch file, or even possibly from an Execute SQL Task. I expect that each of those would effectively let me set the filename of the package AND pass in the configuration filename value. Is that possible? Which of those three would be the best option?

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();Package p = app.LoadPackage(Dts.Variables["ChildPackageFile"].Value.ToString(), null);Configuration config = p.Configurations["Configuration 1"];config.ConfigurationString = Dts.Variables["ConfigurationFile"].Value.ToString()";DTSExecResult result = p.Execute(); That's roughly what I needed...

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();Package p = app.LoadPackage(Dts.Variables["ChildPackageFile"].Value.ToString(), null);Configuration config = p.Configurations["Configuration 1"];config.ConfigurationString = Dts.Variables["ConfigurationFile"].Value.ToString()";DTSExecResult result = p.Execute(); That's roughly what I needed...

I still don't see how a database table will help - you can pass the connection string to the parent package, but not from there to subsequent child packages using the Execute Package Task. As another option - I suppose I could run the child packages from either a script task, or from a batch file, or even possibly from an Execute SQL Task. I expect that each of those would effectively let me set the filename of the package AND pass in the configuration filename value. Is that possible? Which of those three would be the best option? You make the conn string a package variable that gets populated by the master package via the passed config file or a "set" directive of DTExec and then pushed down to the children.Arthur My Blog

I still don't see how a database table will help - you can pass the connection string to the parent package, but not from there to subsequent child packages using the Execute Package Task. As another option - I suppose I could run the child packages from either a script task, or from a batch file, or even possibly from an Execute SQL Task. I expect that each of those would effectively let me set the filename of the package AND pass in the configuration filename value. Is that possible? Which of those three would be the best option? You make the conn string a package variable that gets populated by the master package via the passed config file or a "set" directive of DTExec and then pushed down to the children.Arthur My Blog

You can pass configurations down to children - you just need to do it with SSIS variables, not Connection Managers. What you need to do is set up configurations on your parent package on an SSIS string variable containing a connection string. Configure your Connection Manager in the parent to have an expression on the ConnectionString property and assign it the value of the variable. Now your parent package uses configurations to dynamically modify it's own connections - just "indirectly" by using a variable. In your child packages, set them up the same as the parent - an SSIS variable for the connection string, connection manager uses that variable in an expression on the ConnectionString property. The only difference between child and parent here is that the configurations on the child don't refer to the same storage location as the parent. Instead, you use Parent Package Variable configuration to pass the SSIS string variable down to the child. Talk to me now on

You can pass configurations down to children - you just need to do it with SSIS variables, not Connection Managers. What you need to do is set up configurations on your parent package on an SSIS string variable containing a connection string. Configure your Connection Manager in the parent to have an expression on the ConnectionString property and assign it the value of the variable. Now your parent package uses configurations to dynamically modify it's own connections - just "indirectly" by using a variable. In your child packages, set them up the same as the parent - an SSIS variable for the connection string, connection manager uses that variable in an expression on the ConnectionString property. The only difference between child and parent here is that the configurations on the child don't refer to the same storage location as the parent. Instead, you use Parent Package Variable configuration to pass the SSIS string variable down to the child. Talk to me now on

Wait - I must be missing something here. So you can get the connection string value into a variable in the parent package - fine. And you can assign the value of a parent variable inside a child package to a child variable. But if I set up a configuration in the child package, of SQL Server type, it's not possible to get the connection, table & filter values from this variable - in the parent OR child package. Not if I use Execute Package Task. Correct? Thanks Sean

Wait - I must be missing something here. So you can get the connection string value into a variable in the parent package - fine. And you can assign the value of a parent variable inside a child package to a child variable. But if I set up a configuration in the child package, of SQL Server type, it's not possible to get the connection, table & filter values from this variable - in the parent OR child package. Not if I use Execute Package Task. Correct? Thanks Sean

You pass the conn string to the master package that tells where to connect to grab the config values. If the parent and child packages use the same config then you are all set (each packages reads its own config values). To set the package variable from DTExec command line you can do dtexec /Ser "ServerName" /SQ "SSISPackagehName" /SET "\Package.Connections[ConnectionManagerName].Properties[ConnectionString]";"TextFilePath/Name" Arthur My Blog

You pass the conn string to the master package that tells where to connect to grab the config values. If the parent and child packages use the same config then you are all set (each packages reads its own config values). To set the package variable from DTExec command line you can do dtexec /Ser "ServerName" /SQ "SSISPackagehName" /SET "\Package.Connections[ConnectionManagerName].Properties[ConnectionString]";"TextFilePath/Name" Arthur My Blog

Todd - ignore the original post below. I see what you're saying. But that means every child variable/configuration has to be set as a variable in the parent, and then passed down via Parent Package Variables. Not viable. I need a parent package to be able to set the configuration XML location of the child package. Simple as that - just doesn't seem possible. ------------------------------------------------- Sorry Todd - but I didn't understand your post. I'm pretty new to SSIS... What you need to do is set up configurations on your parent package on an SSIS string variable containing a connection string. Configure your Connection Manager in the parent to have an expression on the ConnectionString property and assign it the value of the variable. Now your parent package uses configurations to dynamically modify it's own connections - just "indirectly" by using a variable. What do you mean, "to have an expression on the ConnectionString property"? How do I "assign it" - using a configuration? Is this only for SQL Server configuration, or other types? Does the connection string have the table & filter in it too (is that possible?)? In your child packages, set them up the same as the parent - an SSIS variable for the connection string, connection manager uses that variable in an expression on the ConnectionString property. The only difference between child and parent here is that the configurations on the child don't refer to the same storage location as the parent. Instead, you use Parent Package Variable configuration to pass the SSIS string variable down to the child. What do you mean by "don't refer to the same storage location"? As you can tell I'm really clueless...

Todd - ignore the original post below. I see what you're saying. But that means every child variable/configuration has to be set as a variable in the parent, and then passed down via Parent Package Variables. Not viable. I need a parent package to be able to set the configuration XML location of the child package. Simple as that - just doesn't seem possible. ------------------------------------------------- Sorry Todd - but I didn't understand your post. I'm pretty new to SSIS... What you need to do is set up configurations on your parent package on an SSIS string variable containing a connection string. Configure your Connection Manager in the parent to have an expression on the ConnectionString property and assign it the value of the variable. Now your parent package uses configurations to dynamically modify it's own connections - just "indirectly" by using a variable. What do you mean, "to have an expression on the ConnectionString property"? How do I "assign it" - using a configuration? Is this only for SQL Server configuration, or other types? Does the connection string have the table & filter in it too (is that possible?)? In your child packages, set them up the same as the parent - an SSIS variable for the connection string, connection manager uses that variable in an expression on the ConnectionString property. The only difference between child and parent here is that the configurations on the child don't refer to the same storage location as the parent. Instead, you use Parent Package Variable configuration to pass the SSIS string variable down to the child. What do you mean by "don't refer to the same storage location"? As you can tell I'm really clueless...

You pass the conn string to the master package that tells where to connect to grab the config values. If the parent and child packages use the same config then you are all set (each packages reads its own config values). To set the package variable from DTExec command line you can do dtexec /Ser "ServerName" /SQ "SSISPackagehName" /SET "\Package.Connections[ConnectionManagerName].Properties[ConnectionString]";"TextFilePath/Name" Arthur My Blog You're passing a TextFilePath/Name to a ConnectionString property? Is that an error? Shouldn't it be something like "Provider=XXX;..."? Or are you passing a UDL-type file location? Still, this is setting a Connection's properties. It doesn't specify the TABLENAME or FILTER for the configuration. Surely that's a key part - that isn't stored in a Connection's ConnectionString? If I wanted to run the package with FILTER=COMPANYXDATA and then run again with FILTER=COMPANYZDATA - how would that fit into your example above?

You pass the conn string to the master package that tells where to connect to grab the config values. If the parent and child packages use the same config then you are all set (each packages reads its own config values). To set the package variable from DTExec command line you can do dtexec /Ser "ServerName" /SQ "SSISPackagehName" /SET "\Package.Connections[ConnectionManagerName].Properties[ConnectionString]";"TextFilePath/Name" Arthur My Blog You're passing a TextFilePath/Name to a ConnectionString property? Is that an error? Shouldn't it be something like "Provider=XXX;..."? Or are you passing a UDL-type file location? Still, this is setting a Connection's properties. It doesn't specify the TABLENAME or FILTER for the configuration. Surely that's a key part - that isn't stored in a Connection's ConnectionString? If I wanted to run the package with FILTER=COMPANYXDATA and then run again with FILTER=COMPANYZDATA - how would that fit into your example above?

I can't see much else you can do here. You can't pass connection manager information from parent to child - there's no "parent package connection" type of configuration (like a "parent package variable" type).You can't set the XML file name on a package's configuration dynamically - it's set at design time through the UI.You can't set a SQL Table configuration's "filter" dynamically - it's set at design time through the UI. Only options I can see are: What I suggested earlier (pass variables from parent to child, then use expressions on CMs to set the connstr)Script Task hacks (pass an XML file location using parent package variables, then use a Script Task to open that file and set CM connstrs... which I'm not sure how/if that'll work...)Use Execute Process Tasks with verbose command lines to set CM conn strs individually. How many CMs are you talking about? (That's the only reason I can think of why you'd think it wasn't viable to use variables.) Talk to me now on

I can't see much else you can do here. You can't pass connection manager information from parent to child - there's no "parent package connection" type of configuration (like a "parent package variable" type).You can't set the XML file name on a package's configuration dynamically - it's set at design time through the UI.You can't set a SQL Table configuration's "filter" dynamically - it's set at design time through the UI. Only options I can see are: What I suggested earlier (pass variables from parent to child, then use expressions on CMs to set the connstr)Script Task hacks (pass an XML file location using parent package variables, then use a Script Task to open that file and set CM connstrs... which I'm not sure how/if that'll work...)Use Execute Process Tasks with verbose command lines to set CM conn strs individually. How many CMs are you talking about? (That's the only reason I can think of why you'd think it wasn't viable to use variables.) Talk to me now on

There are no downsides, this is a legit way of running any packages. Execute Process Task is I guess to run a package by a script? I think you can just use a separate config file for the child package only, or not to use the parent-child model because I see why would you.Arthur My Blog

There are no downsides, this is a legit way of running any packages. Execute Process Task is I guess to run a package by a script? I think you can just use a separate config file for the child package only, or not to use the parent-child model because I see why would you.Arthur My Blog

Your questioning is valid, and they've addressed it in SSIS 2012 - you can now call Execute Package Tasks and pass parameters down to the child from the parent. It's a limitation they didn't fix in 2008R2 and earlier... Go ahead and mark the post that answers your question as the answer - you know what the answer is to your question. (I've got too many points anyway.) Talk to me now on

Your questioning is valid, and they've addressed it in SSIS 2012 - you can now call Execute Package Tasks and pass parameters down to the child from the parent. It's a limitation they didn't fix in 2008R2 and earlier... Go ahead and mark the post that answers your question as the answer - you know what the answer is to your question. (I've got too many points anyway.) Talk to me now on

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();Package p = app.LoadPackage(Dts.Variables["ChildPackageFile"].Value.ToString(), null);Configuration config = p.Configurations["Configuration 1"];config.ConfigurationString = Dts.Variables["ConfigurationFile"].Value.ToString();DTSExecResult result = p.Execute(); I think this is the closest answer - unless someone knows of a serious downside to running a child package from a Script Task? I don't want to "pass variables from parent to child", as (1) there could be a lot of them, which convolutes the parent, and (2) if I ever want to run the child as stand-alone (developing/debugging), I have to manually set each variable. "Script Task hacks" - This is kind of a script task hack, but it still uses the configuration, so it seems better. "Execute Process Tasks" - this is also kind of what I'm doing, if I understand correctly. Seems crazy that "you can't set [child] configuration dynamically - it's set at design time through the UI" - when you CAN set it at run time for the parent package via DTSExec /CONF. You should be able to pass that down to the child. (Not sure if it's acceptable to mark my own post as the answer though?)

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();Package p = app.LoadPackage(Dts.Variables["ChildPackageFile"].Value.ToString(), null);Configuration config = p.Configurations["Configuration 1"];config.ConfigurationString = Dts.Variables["ConfigurationFile"].Value.ToString();DTSExecResult result = p.Execute(); I think this is the closest answer - unless someone knows of a serious downside to running a child package from a Script Task? I don't want to "pass variables from parent to child", as (1) there could be a lot of them, which convolutes the parent, and (2) if I ever want to run the child as stand-alone (developing/debugging), I have to manually set each variable. "Script Task hacks" - This is kind of a script task hack, but it still uses the configuration, so it seems better. "Execute Process Tasks" - this is also kind of what I'm doing, if I understand correctly. Seems crazy that "you can't set [child] configuration dynamically - it's set at design time through the UI" - when you CAN set it at run time for the parent package via DTSExec /CONF. You should be able to pass that down to the child. (Not sure if it's acceptable to mark my own post as the answer though?)