Dynamic Package Configuration File in SSIS

This is in reference to an interesting problem posted on MSDB forum. It was so interesting that i though it worth a post. So here we go:

Problem:

Child Package should use a Configuration file whose path is determined only at the run time of Parent Package. Package Configuration doesnt have any property which may allow it to dynamically determine path at run time and use it.

Solution:

There can be many ways of solving this as suggested by other SSIS pandits but what struck to my mind first was, we can do it by using very basic tasks in SSIS. The approach is to:

In Child Package:

1. Create the Child Package as usual without bothering of Dynamic Config file and store config file to some location say E:\Configs

In Parent Package

1. Get actual path of Config(which is to be used)

2. Use File System Task and copy this actual (Dynamic as I prefer to say) to the location of Child Package Config (E:\Configs)

To Demonstrate this.:

1. I Created a Child package which would access a database table using it Development config file.

2. Now I created a Parent package which will copy the dynamic config to the location at call the child package.

Now i will Run this Parent package- which will make the child package to use the dynamic Config file

Cheers!!