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 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.
|Identifying packages using connection by type and name|
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;
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
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;
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.
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.
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!)