SQL Server Integration Services XML Configurations Tips and Tricks

By:   |   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.

List of Connection Managers for Demonstration

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.

Original XML Configuration File

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.
Enable the HTML Source Editing Toolbar
  • Now click on the button highlighted below which says "Format the whole document".
HTML Source Editing Toolbar - Format the whole document
  • This will turn the XML configuration file into a well formatted XML document as shown below.
Formatted XML Configuration File

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.

Commenting Configurations - Toolbar Comment Icon

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.

Commenting Configurations - XML Configuration File

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.

Commenting Configurations - Updated Connection Manager

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dattatrey Sindol 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



Comments For This Article




Sunday, March 20, 2016 - 9:21:21 AM - Sivarani Back To Top (41008)
  1. I need Migration SQL server 2008 to 2014 process.
  2. Migration of ssis packages from 2008 to 2014 we have two configuration files XML and SQL config files to my package, so while I am pointing to new server faced problem with SQL config how can I  point to new server what is the process of updating SQL configuration can you please help me on this.

 


Wednesday, September 3, 2014 - 5:30:34 AM - L-P Larsson Back To Top (34362)

Thanks! Even faster - the shortcut command sequence to format the xml is "ctrl+a,k,d".















get free sql tips
agree to terms