Friday, April 16, 2010

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 PackageBold
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!!

5 comments:

Anonymous said...

cool

Anonymous said...

Hi,

How to get the path by using execute sql task...
Let me clear this.

Anonymous said...

Hi,
How to get the path by using execute sql task...

aruna said...

Hi Rahul ,
I am new to this feild.How to get the path by using execute sql task...

Rahul Kumar said...

Hi Aruna,
Path will be stored in a SQL table, Use a sql to select that path and inside Execute sql Task store value in the variable
Suggested: Read about Execute SQL TASK

Post a Comment