SSIS: Setting up an XML Configuration File

SSIS provides several methods to create configuration information for your ETL process. The easiest of these to implement is the XML configuration file. Additionally, it provides the easiest method to migrate and update your configuration information of the method available.
I will go step by step on how you can implement this in your application. Below we have a screenshot of a simple application which reads data from a flat file and places the data into a corresponding SQL Server table.

First we will set up a variable in which we will set up to hold the location of our flat file. Right click on the design surface of the Control Flow and select Variables to bring up the variables screen shown below.

Click on the ‘New Variable’ button,first one on the left, to create the new variable. Simply fill out the information for the variable. For our example, we wanted a string variable to hold the location of the our flat file as shown below.

Next we want to actually set our flat file connector to use this variable to determine its connection string. In your connection manager section, right click on the flat file connection and then select the […] button next to Expressions. This will bring up the Property Expressions screen below.

Select ‘ConnectionString’ from the drop-down on the left and then select the […] button under the Expression column to bring up the Expression Builder screen.

Drag and drop the user variable you created into the expression box. Then you may also click the Evaluate Expression button before clicking OK to check that the value you expect is being returned.

Now we are ready to set up the Configuration file for this project. On the Control Flow tab right click and select Configuration Properties. Now check the ‘Enable Package Configuration’ checkbox.

Click Add to bring up the Configuration Wizard as shown below.

Now we can select the XML file from the drop down and select the location of the configuration file as shown below.

Now clicking next allows you to pick the items that you would like to store and be read from the configuration file. We can select our user variable we created. Additionally, you could select things such as the connectionstring for the database.

So now you can simply go to the configuration file and open it in any text editing application to change the values that are used in the package.

See, nothing to it…..


Powered by ScribeFire.