SQL Server Integration Services (SSIS) are really nice ELT tool for data transformation, conversion and transfer. But they not of much use if they can’t be configured to accept different parameters. For example, a once task to move file is designed if it can’t be used to look for file in different folder or FTP task can’t connect FTP site other than the one coded in task itself. In these cases a configuration file generated to be used with DTSX package can be really handy. Configuration files for SSIS package allows us to make our package more flexible (or say configurable). We can store all kind of parameters into this configuration file, which will allow use to change package behavior by changing package parameters.
For sake example, I have created a sample project with file transfer task. This package just looks into specified folder and moves any txt file exist in that folder to another location. This project is using a configuration file which allows user to change target and source folder location, and we can also change what type of file this package will move (e.g. Text, PDF or all files).
Steps to create configuration file:
1) Create three variables using variables window. One will be source folder path, second will be destination folder path and third one is just a file name. (if Variable window is not open, it can be accessed from View –> Other Windows –> Variables)
2) Drop “Foreach Loop Container” into control flow tab. And then place “File System Task” inside of “Foreach Loop Container”. This basically means that this “Foreach Loop Container” will execute that “File System Task” every time it loops through.
3) Configure Foreach loop to look into folder any folder by setting “Destination Folder”. And make “Files” as TEXT files. Also, in Expressions option in Collection make “Directory” as user variable for source path. And choose retrieve file name as “Fully Qualified”. These settings will make Foreach loop container to look for all TEXT files located at path defined in user variable for source path and it will return full path along with file name (e.g. D:\SSIStest\Source\file1.txt)
 |
 |
 |
4) Then open File System task editor and configure it. For this, make both source and destination paths as variables and set destination variable as dest path and source variable as source path. I’ve chosen to overwrite destination if file exists.
Now final step is to create configuration file and make our SSIS package use it.
5) Right click on empty “Control Flow” space and select “Package Configurations”. This will open up “Package Configurations Organizer”. Enable package configurations and “ADD” to create new package. Here we have actually some really neat options to save configurations (e.g. in registry, or in environment variables) etc. I’ve selected XML configuration file option. And select where you want to create that configuration file.
 |
 |
 |
6) Next step will be select which variables to export. This screen actually shows all possible variable that can be used to configure package. Including all user created variables, various task options, different connections etc. Here, I’ve selected options like variable value (which is source and dest path defined in variables) and from Forearch Loop container –> Enumerator Properties –> Directory and FileSpec for source path and file type.
 |
 |
 |
7) Last step will be to save configuration and that is it.
Interesting thing is visually there is no way to tell that package is using any kind of configuration file. I mean the configuration file is not even included into project. So you will only see that file when you right click on empty control flow space and click on “Package Configurations” !!
Good thing about configuration file is, once created it can be edited without opening SSIS solution because it is not included in package itself, and it is just XML formatted file. Also if you are very good with SSIS configuration then you can even create configuration file without package itself !!
<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="Scarface\Gaurang" GeneratedFromPackageName="ConfigFileDemo" GeneratedFromPackageID="{EB0E7BA3-F7A8-4E7F-A1D4-94CCECF13AD0}" GeneratedDate="12/4/2011 6:19:14 PM"/></DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="\Package.Variables[User::FileDest].Properties[Value]" ValueType="String"><ConfiguredValue>D:\Study\DestinationFolder</ConfiguredValue></Configuration> <Configuration ConfiguredType="Property" Path="\Package.Variables[User::FileName].Properties[Value]" ValueType="String"><ConfiguredValue>default</ConfiguredValue></Configuration> <Configuration ConfiguredType="Property" Path="\Package.Variables[User::FileSource].Properties[Value]" ValueType="String"><ConfiguredValue>D:\Study\SourceFolder</ConfiguredValue></Configuration> <Configuration ConfiguredType="Property" Path="\Package\Foreach Loop Container.ForEachEnumerator.Properties[Directory]" ValueType="String"><ConfiguredValue>D:\Study\SourceFolder</ConfiguredValue></Configuration> <Configuration ConfiguredType="Property" Path="\Package\Foreach Loop Container.ForEachEnumerator.Properties[FileSpec]" ValueType="String"><ConfiguredValue>*.txt</ConfiguredValue></Configuration> </DTSConfiguration>
Once package is created it can be used with SSIS package at execution time to make package use that configuration file settings.
Just like this, we can create configuration file for any SSIS package and make it more flexible to operate / use.
That’s it for now.
It’s Just A Thought … 
