SQL Server Integration Services Connection Manager Tips and Tricks

 

Problem

 

I have been using SQL Server Integration Services (SSIS) and I would like to know if there are any tips or tricks that can make Connection Managers more effective for developing or troubleshooting issues.

 

Solution

In this tip, we will take a look at the following Tips and Tricks for Connection Managers:

 

• Adding an "Application Name" property to the connection string
• Creating Two Connection Managers for each Database Connection
• Capturing Connection Manager details in Package Configurations

 

Adding an "Application Name" property to a SSIS connection string

 

When we run multiple SSIS packages, each one having one or more connection managers, the Profiler trace being run against that database connection shows the commands/queries being executed by the SSIS packages against the server/database. With the default settings of the connection manager, it becomes difficult to tell which query is being executed by which SSIS package just by looking at the Profiler trace. Let's see this with an example.

 

For the purpose of this demonstration, I have created an SSIS package with an Execute SQL Task pointing to tempdb on the local instance of SQL Server with the following query being executed by the task.

 

WAITFOR DELAY '00:05:00'

GO

With the default settings in the package, the connection string for the connection manager looks as shown below.

 

DataSource=.;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

 

When we run the profiler trace against the local instance of SQL Server and start the SSIS package, we can see the following trace details.

 

 

As we can see in the above trace, the "Application Name" column is set to "Microsoft SQL Server" by default. As a result of this, whenever we have multiple packages running on the computer, it becomes hard to identify which package is executing which of the queries, just by looking at the profiler trace.

 

To address this issue, we can make use of the "Application Name" property of a connection manager in SSIS. To add an "Application Name", go to connection manager properties and click on "All" in the left pane in the Connection Manager Editor and set "Application Name" property to "SSIS Tips and Tricks" as shown below.

 

 

After making the above change, our connection string (Select the Connection Manager in the Connection Managers tray and go to Properties Window, and check the "ConnectionString" property) should look as shown below.

 

Data Source =. ; Initial Catalog = tempdb ; Provider=SQLNCLI11.1; Integrated Security=SSPI; Auto Translate = False; Application Name=SSIS Tips and Tricks;

 

Now when we run the SSIS package (Keeping profiler ON), we can see that the "Application Name" in the Profiler Trace is updated as "SSIS Tips and Tricks" as shown below.

 

 

This can be really handy in troubleshooting scenarios. Hence make it a best practice to add "Application Name" to your Connection String.

 

Creating Two Connection Managers for each Database Connection

 

Often when we need to connect to any database in SSIS, we create only one connection manager which is mostly an OLE DB Connection Manager (basically a native connection). However, it can be useful to create an ADO.NET connection manager pointing to the same server and database as that of the corresponding OLE DB connection manager.

 

There are various benefits of an ADO.NET connection and one of them being that it makes it very clear and simple to map parameters in an Execute SQL Task. For the purpose of this demonstration, let's create two Execute SQL Tasks which are inserting row counts into an AuditLog table - one with an OLE DB Connection and the other with an ADO.NET Connection.

 

The format in which the query needs to be specified for an OLE DB Connection looks as shown below.

 

 

As we can see in the above query, we cannot specify the parameter names and instead we need to mark the parameters using a "?" mark, which is not very clear. In the parameter mappings window as well, we cannot specify the name of the parameter, but instead we need to map the parameters using numbers starting with 0 through (n-1), where "n" is the number of parameters in the query. The parameter mapping for this query looks as shown below.

 

 

As we can see from the above screenshot, even the parameter mapping is not very clear and when large numbers of parameters need to be mapped it becomes difficult to track and map them and might lead to incorrect mappings.

 

On the other hand, the format in which the query can be specified for an ADO.NET Connection looks as shown below.

 

 

As we can see from the above query, we can clearly specify the name of the parameter, which can be more meaningful (usually the parameter name is the same as the column name) and makes it very clear. The parameter mapping for this query looks as shown below.

 

 

As we can see from the above screenshot, the parameter mapping is very clear as it allows us to specify the name of the parameter and not just the sequence specified as 0, 1, 2 etc.

 

Having two connection managers (an OLE DB and an ADO.NET) for each database connection gives us the flexibility to use different ones for different purposes thereby simplifying the development and maintenance of the SSIS packages.

 

Following are the highlights of OLE DB and ADO.NET connections:

 

OLE DB connection is supported by majority of tasks/transformations in SSIS, but a few tasks/transformations like Bulk Insert Task, Lookup Transformation etc. support only OLE DB connection.

 

ADO.NET connection is supported by many tasks/transformations in SSIS, but a few tasks/transformations like Data Profiling Task, CDC Control Task etc. support only ADO.NET connection.

 

Mapping Parameters in Execute SQL Task is very convenient and clear and easy to understand using ADO.NET Connection.

 

OLE DB connection (being a native connection) is a bit faster than ADO.NET in most scenarios.

 

Among various connection types supported by Execute SQL Task, ADO.NET is the only connection type which allows specifying the parameter mapping clearly as demonstrated above.

 

Capturing SSIS Connection Manager Details in Package Configurations

 

Package Configurations are a very important part of SSIS packages and offer various advantages including easier movement of packages across different environments. The most common type of package configuration is the XML Configuration File.

 

There are various ways to capture the details of a connection manager into a package configuration file and often beginners make mistakes in this step and usually end up capturing individual properties of a connection manager into the configuration file. Let's take a look at this with a simple demonstration.

 

For the purpose of this demonstration, let's create two OLE DB Connection Managers, say TestConn1 and TestConn2, both pointing to tempdb database on a local instance of SQL Server.

 

Next let's capture the connection manager details into an XML configuration file using two different approaches - the first approach for TestConn1 to capture the individual properties and the second approach for TestConn2 to capture a single property which contains all the necessary connection information.

 

Follow the below steps to create an XML configuration file and capture these details.

 

Go to SSIS > Package Configurations.

 

Click on "Enable package configurations" if not already checked.

 

Click on Add, set the Configuration type to "XML configuration file", set the path of the configuration file, and click Next.

 

Select "ServerName", "InitialCatalog", "UserName", and "Password" for TestConn1 and "ConnectionString" for TestConn2 as shown below.

 

 

 

Click Next, give a name to the configuration in the "Configuration name" textbox and click Finish.

 

Now go to the folder containing the configuration file and open the configuration file. The configuration file looks as shown below.

 

 

Note: Passwords are not captured in the configuration file as part of the configuration file creation (for security reasons) and they need to be manually updated in the file.

 

As we can see in the above screenshot, just by capturing the "ConnectionString" property of the connection manager into the configuration file, all the necessary connection information becomes available in the file and this makes the configuration file simple and easy to manage.

 

As we can see in this tip, with simple steps, we can make effective use of Connection Managers and Package Configurations in SSIS.