SSIS Package Configuration - XML File Configuration

Scenario - In SSIS, keep all the information regarding connection string(connections string, file path, etc..) into XML configuration file is under good practice. In my current project we have two type of work environments like as as Test as well as Production. As soon as we get the data file from FTP, it is required to upload the data into the Test server first for data validation. If everything is fine then same data file will be uploaded into the Production server database. 
So switching between Test to Production server database, it is better to maintain a XML configuration file rather than changing the value of database connection string in Main package directly. 

Follow the below steps in order to enable the XML file configuration in SSIS package -
Step 1 - Create a variable in the package which will hold the value from XML configuration file. Let's variable name is StrCon.
Step 2 - Right click on the package body and select package configurations.
Step 3 - Check the Enable package configuration from the checkbox and click on Add button.

(Refer the below screen shot for reference)

Step 4 - Select the XML configuration file as configuration type from the dropdown list.

(Refer the below screen shot for reference)

Step 5 - It is required to maintain the path for XML configuration file, So there are two ways to achieve this
1. Direct path
2. Indirect path(Hold the file location into environment variable)

Direct path - It contains the hard coded path of the XML configuration file. If in case it is required to change the file path we have to  it manually. We use this option if file path will remains same for ever. 

To implement the direct configuration follow the below point
1. Check the first radio box Specify configuration settings directly and then hard code the file path using browse functionality(Extension of the XML  configuration file is dtsConfig) then click on Next.

(Refer the below screen shot for reference)

2. Assign the package variable value StrCon with XML configuration file value.

(Refer the below screen shot for reference)

3. Provide the name of the configuration and click on Finish.

(Refer the below screen shot for reference)

Now Direct path XML configuration has been completed successfully.

Indirect path - Here an environment contains the file path. So if file path needs to change in future, we need to change in the environment variable value.

To implement this, Follow the below points -

1. Create an environment variable and provide the proper path of XML configuration file, Let's say variable name is ConnectionString.

2. Once environment variable is created then close and reopen the SSIS solution otherwise may environment variable changes will not affect.

3. Right click on package body and select package configurations and select the recently created XML direct configuration link and click on edit

(Refer the below screen shot for reference)

4. Check the second radio button Configuration location is stored in an environment variable and select the correct environment variable, which is ConnectionString then click on Next.

(Refer the below screen shot for reference)

5.Provide the name of the configuration and click on finish, so earlier configuration will change from direct to indirect now.

(Refer the below screen shot for reference)

XML file configuration has been created successfully.

How to achieve value from the XML configuration file -
In order to validate the XML configuration file we will create a script task and fetch the configuration value from XML file configuration in package variable and then populate the value.

Follow the below the points -
1. Drag and drop a script task from the Toolbox.

(Refer the below screen shot for reference)

2. Double click on it and provide the proper ReadOnlyVariables and then click on Edit button.

(Refer the below screen shot for reference)

3. Write the below code at Main method

MessageBox.Show(Dts.Variables["StrCon"].Value.ToString());

4. Go to XML configuration file and reset the value of <ConfigurationValue> node as below-

<ConfiguredValue>Sample Connection String</ConfiguredValue>

5. Now run the package if you see the popup it means your XML direct/Indirect configuration is completed successfully

(Refer the below screen shot for reference)

e

If you see the above popup, it means your XML configuration value has been set successfully.

Note -
1. Extension of XML configuration file is .dtsConfig rather than .xml. 

2. Environment variable configuration generally we edit the existing link which was created for direct XML file path configuration.

I hope this article will make sense to the readers, Please mark your comments.