Package configuration

In simplest terms a configuration is a way to modify a property without opening a package.  This can apply to almost everything inside your packages.

I am taking simple example of Connection managers because that is where configurations are most commonly used.  Think of the properties of a connection manager.  There is a name, a connection string, an initial catalog, server name, username, password and some other things as well. A configuration can be put on one or multiple properties.  So you can configure the server name, which is a very common property to change, or the username and password. 

A configuration is going to be stored someplace outside the code that makes up the SSIS package.  This may be in an environment variable on a server, or more commonly in either an XML file on the file system or in a SQL Server table.  When you make a change in the external location the next time the package is run it will use the new value that you entered.

 The reason for this: as you migrate the package from DEV to QA and ultimately production you have to change the server where the data is being pulled from.  A package running in production using DEV data is absolutely useless.  So, rather than having to open all your packages and change the value of the server name (from DEV Server to PROD Server for instance) you can put a configuration on the server name property and change the value inside the configuration file.  

In SSIS there are following way to configure a package.

Ø  XML Configuration file

Ø  Environment file

Ø  Registry Entry

Ø  Parent package variable

Ø  SQL server

We can use configuration type on our requirement. Here we will see all type on configuration one by one. Before going to explain configuration type in details we will show how can set the configuration file.

There is two way to get the configuration file

1.       Right click on control flow you will get following screen. Select the package configuration.

2.       Second click on SSIS on menu you will get the list from that you select package Configuration.

Here I am going to take example of the file system task. In file system task we need two connection strings Source connection and the destination connection.

Create two variables which store the value of the source and destination connection.

Now you got to the properties of the variables and set value.

After the click on the expression tab you will get the following windows.

Now in variables section you can select user variables and click on the evaluate expression you will get the value of the connection string.

Similarly you need to map all variables.

XML Configuration file

Now Write click on control flow and select package configuration.

Click on Add button

Click on Next button.

Select configuration type as xml configuration file.

Select the file path where you want to save your configuration file. The extension of the Config file is .dtsConfig 

Click on next.

In this session you need to select the properties list which you want to export. Mainly we take name and value of the variables. Select for all variables. Click next.

Click finish.

Click on close button of package configuration organizer. Not you have created a XML configuration file.

See XML configuration file has been created. I have named as FileSystemTask. Here if you want to open and edit you can do it. The changes are reflected on the package.

Now I am going to execute the package.

It’s executed successfully.

In this package I have taken as source path as “C:\Users\BAGESH\Desktop\MSBI\6.1 SSIS_2012”

Destination path as “C:\Users\BAGESH\Desktop\New folder (2)”

Now I am going to change the values of the source and destination path in Config file.

Source path as “F:\my study materials\6.1 SSIS_2012”

Destination path as “E:\New folder”

Save it.

Before execution of the package.  

Now execute the package. Package executed success fully.

Now see the destination

SQL Configuration

Right click on the control flow

Package configuration organizer click on Add after that click on the next.

Select the configuration type as SQL server.

Create new connection.

Click on new connection

Write the server name.

Select the database where you want to store the Config values. Click on ok.

Now select the configuration table. if you have the configuration table select that table otherwise click on new button you will get the structure of the configuration table. Click on the ok.

 Write the configuration filter.

Click on the next.

Here you need to select the values of the variables which you want to store in Config files. As XML Config here we store the name and the value of the variables.

Click on next.

Configuration file has been created.

Click on Finish.

Close the package configuration wizard.

Execute the package.

Executed successfully.

Open the Sql sever table

You will get the name and value of the variable.

Now I am going the changes the value of the source and destination in the table. It will be reflected on the package also.

Now I have changes the value of source and destination.

Source path as “F:\my study materials\6.1 SSIS_2012”

Destination path as “E:\New folder”

Execute the package

You will get the file in new destination.

Environment Variable

In case you are going with Environment variable then you need to have same Config  file for all the SSIS packages .Environment variable should be present in all your  environments (test ,dev,prod).you need to have permission to create these variables. Package Configuration file per Package. The problem with environment variable is it each variable can store only one property. So if more than one property needs to store then you need to have different variables for it.