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 Connection Manager Tips and Tricks


By:   |   Last Updated: 2013-10-03   |   Comments (6)   |   Related Tips: More > Integration Services Connection Managers

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

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 these tutorials SQL Server Integration Services (SSIS) and SQL Server Integration Services (SSIS) Connection Managers.

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.

Data Source=.;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.

Profiler Output without Application Name

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.

Application Name in the Connection Manager Properties

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.

Profiler Output with Application Name

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.

Parameterized SQL Statement for OLE DB Connection

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.

Parameterized SQL Statement for OLE DB Connection

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.

Parameterized SQL Statement for OLE DB Connection

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.

Parameterized SQL Statement for OLE DB Connection

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.

    Connection Managers Captured in Configuration File

  • 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.

SSIS Package Configuration File

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.

Next Steps


Last Updated: 2013-10-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.



    



Tuesday, February 10, 2015 - 3:19:45 PM - G Back To Top

SQLNCLI11.1 or SQLNCLI11.0? What's the difference? When is it appropraite to use one over the other?


Monday, January 12, 2015 - 9:01:03 AM - Scott Back To Top

I use SQL table configurations, and my SQL connections are usually Windows authentication.  So my configuration only needs to include the ServerName and InitialCatalog properties, and there is a separate configurate group (ConfigurationFilter value) for each server and database.  This way the configuration set can be reused by any package and also by either ADO.Net or OLEDB connection managers.


Monday, June 30, 2014 - 9:04:15 AM - Aravinth Back To Top

*********************please help me***************

I want to use a parameter as a connection manager in ssis....

if I want to change the source connections , I m only change the parameter value . that I wanted to do in ssis ..

if any one know this please help me ....

send the suggestions to my mail id: [email protected]


Friday, October 18, 2013 - 9:39:45 AM - Bob Feldsien Back To Top

RE Phillip Harris comment, I always use named parameters when calling a stored procedure from the OLEDB connection and I make a point to teach junior developers to do the same.  That way, if the stored procedure parameters change slightly it won't necessarily break the package.  For example if a new parameter is added with a default value the package can safely ignore it.


Wednesday, October 16, 2013 - 5:09:46 AM - Philip Harris Back To Top

Re OLE DB Connection Manager -

Using OLE DB Connection Manager, I have a data flow component using a SQL Execute command written thus in the "SQL command text" box:

EXECUTE xxxx.dbo.yyyyy
 @PeriodStartDateTime = ?
,@PeriodHours = ?
,@DefaultAntHeight = ?
,@IncludeSpecial = ?
,@IncludeCommitted = ?
;

I can specify the @zzz names in the "Set Query Parameters" window,
      e.g. Parameters: @PeriodStartDateTime ; Variables: User::PeriodStartDateTime 
and it works fine. (I have entered the parameters there in the same order as in the command text.)

Is this a special case or could the example of INSERT INTO... be coded in a similar way? (I'll try it some day when I've got time :-) )

 


Friday, October 04, 2013 - 8:27:49 AM - Koen Verbeeck Back To Top

* Regarding ADO.NET vs OLE DB: ADO is also much easier to handle in a script task. For example, when reading from an SSIS object variable. For more info: Exploring the System.Object Package Variable

* Regarding storing only the connection string in a package configuration (by the way, any official statistics on the fact the XML is the most used one? In a lot of cases the SQL Server Table config is used): if you store only the connectionstring, your package will throw warnings when you upgrade it to a new version of SSIS. When you upgrade, the native client version number is incremented in the connection string. However, you will overwrite this upgrade with the hard-coded native client value in your configuration. If you store only the server name and the initial catalog, there are no issues when the package is upgraded.


Learn more about SQL Server tools