By: Dattatrey Sindol | Updated: 2014-09-03 | Comments (2) | Related: > Integration Services Configuration Options
Problem
Package Configurations are a very powerful feature of SQL Server Integration Services (SSIS). They enable us to handle various things dynamically at run-time. In this tip, we will take a look at few Tips and Tricks on SSIS XML Package Configurations.
Solution
In this tip, we will take a look at the following Tips and Tricks on SSIS XML Package Configurations:
- Formatting the XML Configuration File for Improved Readability
- Commenting Select Configurations in XML Configuration File
- One XML Configuration File for Each Shared Connection Manager
Note: This tip assumes that you have experience in building simple to medium complexity SSIS Packages and are familiar with the concept of Connection Managers and Package Configurations. If not, check out the SQL Server Integration Services (SSIS) and SQL Server Integration Services (SSIS) Connection Managers Tutorials.
Formatting the SSIS XML Configuration File for Improved Readability
Package configuration files are a very powerful feature of SSIS. It allows us to make many aspects of our packages configurable and simplifies the movement of code across different environments (Development, Test, Production, etc.). However, one of the drawbacks of generating configuration files using Visual Studio is that the configuration files are not well formatted thereby leading to reduced readability. Let us take a look at a trick which we can use to quickly format a configuration file and make it more readable.
For the purpose of this demonstration, I have created a set of OLE DB connection managers pointing to AdventureWorks and AdventureWorksDW databases using SQL Server and Windows authentication as shown below.
Now, let us create an XML configuration file and capture the connection strings for the above list connection managers. The XML configuration file when opened in Visual Studio (SQL Server Data Tools) looks as shown below.
Let us format this configuration file to make it more readable. Follow the below steps to format this configuration file.
- Open SQL Server Data Tools (or Visual Studio).
- Go to File >> Open >> File...
- Browse to the location where XML Configuration File is saved and open it.
- Right click on the toolbar and enable the HTML Source Editing Toolbar as shown below.
- Now click on the button highlighted below which says "Format the whole document".
- This will turn the XML configuration file into a well formatted XML document as shown below.
As we can see we can easily format the configuration file in a few simple steps within Visual Studio, without requiring any other tools, and make the configuration file well formatted with improved readability.
Commenting Select Configurations in the SSIS XML Configuration File
During development and unit testing phase, we sometimes tend to modify the configuration values temporarily for testing purposes. For instance, we create a new database and want to point our connection manager to it temporarily. In such scenarios, the ability to comment the configurations in XML configuration file comes handy. Let us take a look it with an example.
In the configuration file created above, let us comment the connection manager "Local_AdventureWorks2012_WIN". To do this, select the entire configuration property (Between "<Configuration ..." and "</Configuration>") for "Local_AdventureWorks2012_WIN" connection manager and click on the "Comment out the selected lines" button to comment the text as shown below.
After commenting the connection manager, let us copy those three lines for "Local_AdventureWorks2012_WIN" and add them to the configuration file just below the commented lines and then update the Initial Catalog to "AdventureWorks2012_New". The commented and new configuration properties for "Local_AdventureWorks2012_WIN" connection manager look as shown below.
Let us open the package to see whether this commented configuration and the newly added configuration for "Local_AdventureWorks2012_WIN" connection manager is reflected or not. When we open the package and verify the properties of the connection manager, we can see that the Initial Catalog for this connection manager is updated as shown below.
This commenting feature can be really handy especially when we want to temporarily modify multiple configurations/properties in a configuration file.
One Integration Services XML Configuration File for Each Shared Connection Manager
Often we use more than one connection in each SSIS package. For instance, in case of large data integration ETLs like the Data Warehousing ETLs, multiple sources and hence multiple connection managers are involved across different packages. One package pointing to multiple connections and one connection being used in multiple packages is a common scenario. In such scenarios, it is recommended that we create one XML configuration file for each connection so that the same configuration file can be re-used across multiple packages. This makes it easy to update the connections while moving the packages from one environment to another environment as we only need to update one XML file for each connection.
Next Steps
- Try the tips and tricks demonstrated above and explore more possibilities.
- Check out the following tips:
- Check out my previous tips
About the author
Dattatrey Sindol has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-09-03
