Extracting & Managing SSIS Connection Information

I have recently been involved in a large project that created a large number of SSIS packages which in turn lead to an extremely large number of connection managers. In BIDS this is unmanageable! True, SSIS does allows the creation of a shared data sources which can in turn be added to a package (essentially creating a project data source), however, the use of these data sources does have some drawbacks. Firstly, when the package is saved; it saves the connection string associated with the data source. When the data source is changed, the package retains the old connection until the package is reopened in BIDS. Secondly, the connection is lost when the package is published (to the file system). Furthermore, there is no convenient method of obtaining information about the connections amongst the packages. To manage the connections, I created a small app that has the following features.

The APP

The lists all packages I in a directory (listed in the right pane). In doing so it builds a library of all connections in the packages. So (for example), if we chose a connection type, only the connections that have that type (and the packages that have those connections) are retained in the connection and package list box.

If we then chose a connection, only the packages that use that connection are highlighted. Because I was looking for consistency in the connection strings, I show the first package connection string and identify if there are any different connection strings amongst the other packages (by highlighted by a salmon colour).

Since the goal is to manage the connections I’ve also included a ‘New Connection String’ change mechanism, a check to evaluate expressions (as connection strings) and the ability to output a csv report for all connections and packages in the library.

Default Load

Identifying packages using connection by type and name

Code Basics

The principle part of the code revolves around the ManagedDTS library which can be found in the following location / dll ‘C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll’

This assembly allows all SSIS (or dts) management including the creation, alteration and control flow (see http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.aspx ).

In order to alter an existing package we need to create an Application instance and use it to load the package. The following code achieves this;

Microsoft.SqlServer.Dts.Runtime.Application _app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package _p = _app.LoadPackage(this_is_my_file_name, null);

The connection managers within the package can then be managed with the connectionmanager class (for example iterating over them by)’

foreach (ConnectionManager _c in _p.Connections)
{}

or by referring to a specific connection through the connectionmanager class and specifying its index, identity or name;

ConnectionManager _c = _p.Connections["other_oledb_1"];
ConnectionManager _c = _p.Connections[0];

Finally, we can use the properties / methods to determine the connections type, connection string, expression (connection string expression);

_c.ConnectionString; // connection string
_c.CreationName; // type
_c.GetExpression(“ConnectionString”);     //expression

Setting properties is relatively easy (just by setting the property value). The only exception to this is setting the connection string expression (which required a call of the .SetExpression method);

_c.ConnectionString = connection_string;
_c.SetExpression(“ConnectionString”, expression_value);

The interesting thing I found for this was that when the connection manager has a connection string expression attached to it, the connection string will not be changed unless the expression is changed. Then when the expresssion is changed, the connection string also changes. I thought this was a little weired.

Configuration Files

There may have been scope in the project to use configuration files however, the auditing requirement and the use of child package execution causes a failure within the child package before the configuration was applied. That is, there are some audit events that are fired before the connection can be changed and where these would otherwise err, the child package fails.

Code

I am happy to share this project with anyone that wants it. Just drop me a line and I’ll send it through (subject to the standard disclaimer!)

Dynamic Package Configurations (Part 2)

This article continue discussion about a method of dynamically assigning variable values at runtime Apply the Record Set to Applicable Variables

Initial State

The initial states of the string variables are set to C:\ with variables being used for the record set container.

The code in the Show Values script tasks is simple and only there to demonstrate a variable values. This is the same code for all three ‘Show Value’ scripts. Note that the variables are passed to the script via the default ReadOnlyVaraibles inputs for the script task.

Assign the Record Sets to the variables (Object Container)

The Execute SQL tasks (‘Get Params (1)’ and ‘Get Params (2)’) are used to pass the query to a container. The queries are not complicated (below) and the standard methods for assigning a record set to a variable apply. These are to ensure that the result set is set to ‘Full Result Set’

Query for Each Result Set

Assign Result Set to Variable

There are two requirements to assign the result set to a variable. Firstly ensure that the result set is set to ‘Full Result Set’ and secondly, that the variable is mapped to the result set.

The two script tasks (‘Assign Params (1)’ and ‘Assign Params (2)’) both contain the same code and only expose the record set variable. One of the main benefits of this technique is that the assignment script does not need to know ahead of time what variables are going to be assigned. This is deemed by the field ‘variable_name’ in the record set result.

There are two main pieces of code in this script task. Firstly, we assign the record set variable (variables) to a data table (in order to iterate over it) and secondly, we iterate over the table and assign variable a value.

Assign the Record Set to a Data Table

The code to assign the record set to the data table is below.

Assigning the Dataset To Variables

The code to assign the data table to variables is below. The iteration of data table (rows) shouldn’t be too much of an issue here or should the checking for package variable name but the key takeaways are that we

1. Check to see that the variable exists (using VariableDespenser.Contains)
2. Lock the variable (VariableDespenser.LockForOneWrite)
3. and then write the value.

Benefits

There are other methods of variable assignment that can be used to change the value of variables at run time, for example, we could assign values in a execute SQL task based on a single row. However, I think that limitation with an approach like this is that task has to be configured ahead of time and the variable names must be known. This method allows variable assignment based solely on the passed record set and as such is very reusable.

I should also point out that this example assumes a sting data type. The code can be extended to permits user defined data types so the record set specifies the data type however this was not included for brevity.

Dynamic Package Configurations (Part 1)

Introduction

In the following two posts I show a method of dynamically assigning variable values at runtime with minimal user intervention. I consider this method of assignment dynamic because the package (and tasks) do not require specific configuration for to the variable that is being altered. Variable values are changed based on the variable names in a record set, and this offers an extremely high degree of flexibility in packages.

The use of SSIS configurations and variable assignment are convenient way to change the value of package variables at runtime.
Configuration files can be created through the menu path <SSIS><Package Configurations> or by selecting the ‘Package Configuration’ option after a right click in the control flow and variables can be assigned a runtime value by using the /set switch in dtexec (either command line or job execution text (as an agent job)). Coincidently, the package configurations can also be set a runtime using the /Conf switch.

However, a possible downside of these utilities is that variable values are set at runtime for the entire package and cannot be changed throughout package execution. In this blog, we will show a method assigning indeterminate variables at runtime without user intervention. This is achieved by

1. Obtaining a record set for the variables (and values) that should be changed (and)
2. Assigning the package variables values based on the record set parameter names.

This is a two set process that retrieves a list of variables to change (and their values) and then iterates the list, changing the variable identified on each iteration. Also, although the code presented here uses a query to retrieve these values, I find a stored procedure (with param) is a much cleaner way of retrieving the list.

Overview

To demonstrate this works, we will create a simple package that will dynamically change value of variables ouput_path and input_path at run time. We show the start values, first assignment values then final assignment values. This is shown in the mark up below;