Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Integration Services XML Configurations Tips and Tricks


By:   |   Last Updated: 2014-09-03   |   Comments (2)   |   Related Tips: More > 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


Last Updated: 2014-09-03


next webcast button


next tip button



About the author
MSSQLTips author Dattatrey Sindol Datta has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, March 20, 2016 - 9:21:21 AM - Sivarani Back To Top
  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 03, 2014 - 5:30:34 AM - L-P Larsson Back To Top

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


Learn more about SQL Server tools