Configurations and Child Packages

Hello,

I was hoping someone might be able to clarify how configuartions work with child packages. My process has 3 levels of packages. The main packages called two child packages each of which calls more child packages.

I'm using Configurations to pass variables from parent packages to child packages. But each level of packages contains Data Flow Source/Destination connections. I was planning on having only 1 XML Configuration file in the main package that would allow the data connections for the main package to be configured for the target enviornment. When the job is scheduled I will be able to point a configuration file for use with the main packages. But will that configuration file be applied to all child packages that will run? All of the Connections Managers have the same name so I'd like to make the change in one file.

If not, am I forced to define a parent/child variable to pass the Connection String from the parent to each of the child packages? Use an expression to set the Connection String dynamically? This seems like a lot of extra work, so I'd really like to see if there is a way to do it with an XML configuration file. Even if I have to definte the same config file for each of my 20+ packages.

Thanks

-Dan

Dan,

It is eminently possible for multiple packages to use a single configuration file - I use this technique all the time. What difficulties are you having in doing this?

Tip: Indirect configuratoins can really help because once you have set them up, "picking" them for use in other packages is dead dead simple.

-Jamie

|||

I guess my confusion is just in how it will work. Perhaps I can boil it down to 2 simple questions.

1. Do I specific in every one of my packages, an XML Configuration file and point them all to the configuration file I created containing all of the common Connection Managers? Or will a child package automatically inherit the configuration of its parent package?

2. When it comes to deployment, I will schedule a job in SQL Server and I will select the XML configuartion files to use for the main package. Will the child packages know to use the same configuration file as well?

I haven't been able to run this package yet as I'm doing my final cleanup for configuration and deployment. I'm trying to get a handle on how this is going to work with the child packages to know how to finish this process. I've used configurations before but only in single package solutions.

|||

infrandom wrote:

I guess my confusion is just in how it will work. Perhaps I can boil it down to 2 simple questions.

1. Do I specific in every one of my packages, an XML Configuration file and point them all to the configuration file I created containing all of the common Connection Managers? Or will a child package automatically inherit the configuration of its parent package?

Yes to the first part of that question. No to the second part.

Which is a shame actually. I know that the subject of scoping connection managers to containers was discussed but not implemented in SSIS2005 due to time constraints. It would certainly be nice to be able to do this in order that connection managers behave just like variables and connection managers in a package can be "seen" by descendant packages. If you want to raise a ticket at Microsoft Connect requesting this functionality for a future version then I will definately vote for it.

infrandom wrote:

2. When it comes to deployment, I will schedule a job in SQL Server and I will select the XML configuartion files to use for the main package. Will the child packages know to use the same configuration file as well?

I think that is kinda answered by my answers to the previous question.

infrandom wrote:

I haven't been able to run this package yet as I'm doing my final cleanup for configuration and deployment. I'm trying to get a handle on how this is going to work with the child packages to know how to finish this process. I've used configurations before but only in single package solutions.

Sounds like you're not far away

|||

Thanks for the clarification Jamie. I was was pretty sure that I would have to add it into each of the packages. Here I go, another round through them all.