Well, the correct answer to this question is – “That Depends“. There are lots of things requires your attention when you start making your package configurable.

For better illustration, lets take e.g. of one simple scenario. I have a package named “TransferDataPackage.dtsx“. This has only one data flow task in which, i have a OLEBD Source and OLEDB Destination tasks. In short, i am transfering data from “SourceServer” to “DestinationServer”. (Names in double quotes are exact names of entities used in example) And now i want to make this package configurable. Demo project is shown below:

Then i have added one data flow task.

Then in DataFlow tab, i have used OLEDB Source and OLEDB Destination tasks to complete the operational flow. (i.e. We want to transfer some data from 1 server to another server.) I have also reanmed the connection managers for better readability.

Now, I will add variables to hold the connection strings for both – source and destination servers, so later I can consume that connection strings into connection managers. So I will go to Control tab -> right click on designer space -> select variables.

In variables pane, I will create 2 variables and fill it with the complete connection strings for source and destination servers respectively.

I will select SourceServerConnectionManager from the list of Connection Managers. and Go to its properties.

In properties pane, Click Expressions -> Select Connection String property.

Click on the elipsis next to the property -> you will see dialog box. Expand variables section and find out SourceServerConnectionString. Drag-drop into Expression area and evaluate for confirmation. It should show the value of that variable in “Evaluated Value” section.

Do same for the DestinationServerConnectionManager. And run the package. Package should run properly.

Our purpose is to define the connection string into variable and then expose this variables outside via configurations, so we can change the connection string for source and destination server. BUT if we expose the whole connection string outside for use to change then users may make mistakes writing the correct connection strings. So to avoid this issue, we will break down the connection string in 3 sections. 1. ServerName 2. DatabaseName and 3. ConnectionString. So, we will create 2 more variables to hold ServerName and DatabaseName and on the basis of 1 and 2 we will create ConnectionString.

We have defined the variables and we have filled the correct values in it. Now, lets make the ConnectionString variable dynamic. Now, you have to select “SourceServerConnectionString” and go to its properties. Click on Expression elipsis button -> this will open expression builder dialog box.

In Expression selection, write whole connection string in double quotes. (Because this is using VB scripting). And then remove the ServerName and DatabaseName sections as shown in the above screenshot. And drag-drop ServerName and DatabaseName variables in the place holders like shown in screenshot below. Dont forget to evaluate and verify the connection string.

After successfully evaluating, exit the dialog box by clicking on OK. There is still one setting to be done for variable property named “EvaluateAsExpression”. Make it to True. By making it True, the SSIS will always use the expression defined in “Expression” property.

Please repeate the process for “DestinationServerConnectionString”.

Now, we will expose the ServerNames and DatabaseNames of Source and Destination to outside so, users can change them. Go to Control Tab -> Right click on designer area -> Select Package Configurations.

Then you will see the Package Configurations Organizer dialog box. By default the checkbox is disabled. You have to enable it. Then click on Add button to add configuration to package.

This will launch the Package Configuration Wizard. Click on Next to proceed.

You will see 5 types of configurations. In this example we will store the configurations into SQL server, So select SQL server.

Select the connection manager in “Connection” list, where you would like to store your configurations. You can also create a new database for storing all your configurations, which is also a good practise to have a centralized configuration store. You can do that by clicking on New button next to the drop-down list.

Now, here one thing happened. In “Connection” i have chose ConfigurationServerConnectionManager as my connection. Well, i have created a new connection string variable in package. Then i have created a new OLEDB connection and override the connection string property from the ConfigurationServerConnectionString variable. You can check this in below screenshot.

Ok, now back to “Package Configuration Wizard”. After selecting the Connection, we have to provide the name of the table where all the configurations will be stored. Here Microsoft guided us by providing the table structure – This is just to ensure, how much configurations have to be stored. So if you have already created table, then you can select it from the drop-down list OR you can click on New button to create new table. In my case, i will click on New.

As you can see, It will show you (suggest you) the structure of the configuration table, but if you want then you can change it. By default the name of the table is [SSIS Configurations]. Well, i dont mind using the same name but i would recommend you to remove the space betweent he words SSIS and Configurations.

Then its time to provide the Filter name. We need filter to distinguish between different package’s configurations from the one single table i.e. SSISConfigurations. I mean, we can store configurations of multiple packages into one single table and they all will be differentiated by filter keyword.

Here in our case, we will provide “TransferDataPacakageFilter”. You can provide anything you want unless its unique in the table.

When you click Next, you will see all the list of objects on left side and property attributes on right side. You have to select those values which you wanted to expose outside OR those you wanted to store as configured values. e.g. we will choose values like ServerName, DatabaseName for our package. There are other properties too, but for now we dont need them to be exposed outside to users.

Click on Next, Provide good name to the configurations. And click Finish.

Now, you can query the database table and check the stored configurations.

We will add one more configuration to expose the Configuration ServerName and DatabaseName. And this time we will store them in XML file. So, just follow the steps shown as below:

Provide a good name to your XML file, which will hold ServerName and DatabaseName of your configurations Server.

Save and click Next.

Select value of ConfigurationDatabaseName and ConfigurationServerName and click Next.

Provide a name to your configuration and click on Finish.

Finally, you will see two configurations added into “Package Configurations Organizer”. Then click on Close. Your package is completely configured now 🙂

Open the XML configuration file in internet explorer to view the details. (For editing this file, you can open it into Notepad.)

You can run and verify the functionalities of your package. Your package is ready for deployment.

I hope this article has given you the brief insight of configuring the SSIS package.

 

Thanks, Khilit