Changing a Connection Manager’s Conenction String using C# – SSIS 2008

A SSIS package can be run using C#, or basically any CLR language. The piece of code depicted in the following image calls a package, tries to execute it, and prints any error or exception that occurs dutring execution of the package to output:

Simple and easy!

Now suppose our package has 2 connection managers: one named “OLTP Source” and another one named “DW Destination”. And let’s suppose that OLTP Source connection manager is created prior to DW Destination, and therefore it is listed first in the Connection Managers pane of BIDS.

The connection managers in a package are accessible in C# code using Connections collection. They are accessed using an index to enumerate in the collection. For example, Pkg.Connections[0] points to the first connection manager listed in the Connection Managers pane. And the connection string of each connection manager can be set by editing connection manager’s ConnectionString property.

The following line of code sets the connection string of the second connection manager in the package (in our example, DW Destination):

pkg.Connections[1].ConnectionString = “Data Source=localhost;Initial Catalog=DW;Integrated Security=SSPI”;

This scenario can be used to update any property of any object in the package when the package is going to be run using a CLR language.

Advertisements

About Saeed Barghi

Big Data/DW/ETL Consultant based in Melbourne, Australia View all posts by Saeed Barghi

2 responses to “Changing a Connection Manager’s Conenction String using C# – SSIS 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: