Setting Up dynamic property value at SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

SSIS offers more flexibility and debugging feature that doesn’t exists in previous ETL version (DTS). This article I will let you know one of property of each component or task that makes your SSIS package more flexible through Expression Editor and more configurable ever than before. Even you can change your parameter value from XML file.

For example, I would like to iterate each files in particular folder, and I make this particular folder is configurable, so I have change input parameter from outside SSIS package, input parameter will be got from outside SSIS environment for example, from XML file or through command line.

Let’s jump into new SSIS package for making it clear for you. First, you define package level type SSIS variable. Name it DirectoryName and FullFileName, and their types are string.

Then you drag For Loop Each container component, and configure its property through Foreach Loop Editor. At Collection section, you define whatever string inside Folder textbox, and make sure Fully Qualified radio button at Retrieve File Name section is selected. Now the dynamic part is located in Expressions of the component. Click on it to display Property Expression Editor. On Property column select Directory and on Expression click ellipsis button to display Expression Builder, and drag variable DirectoryName from Variables list box to Expression textbox, you can click Evaluate Expression button to make sure before you go back to Expression Editor window by clicking OK. Now you can change DirectoryName not directly into Foreach Loop component, but from variable.

The task has output of full file name (with folder path), and you need to provide variable that will receive return string value from the task. Let’s go to Variable Mappings, and set Variable column to variable FileName, and click OK to go back to Control Flow designer. Drag Script Task into Foreach Loop Container task, and open its editor, on Script section specify FileName in ReadOnlyVariables because I just want to pop up its value through message box. Click on design script button to open another new Visual Studio IDE, and type MsgBox(Dts.Variables(“User::FileName”). Value.ToString()), close it.

Now you fill in DirectoryName variable, that you want to pop up all files inside the directory. Run the package. And it will pop up file names whose files reside in the directory.

Now you need to expand your DirectoryName variable to outside SSIS package, so each time you don’t have to edit your SSIS when directory name changes. Choose SSIS menu and select Package Configurations. Click Add button.  Choose XML configuration file type and specify its name, on next screen you choose Value property of DirectoryName.

Now you can specify command line that calls the package as below:

DTEXEC /File “your dts file path” /CONFIGFILE “your xml config file path” /MAXCONCURRENT “-1” /CHECKPOINTING OFF /REPORTING N

With command line specification above, you can achieve same behaviour as it is in Visual Studio IDE. Your package parameter doesn’t tightly coupled with its package by extracting out to XML file, so you can change the value in XML file without having to change to package.