Use an OLEDB Connection Manager in an SSIS Script Task

I was recently required to utilize an OLEDB connection manager within a script task.  The package uses a package configuration to dynamically assign the connection string for the connection manager.  The script task uses the same connection to poll the status of a service that is maintained in a table in the database and I wanted to insure that the connection was based on the same connection manger to insure portability.

This seemed rather straight forward and I immediately was pointed towards a blog post by Todd McDermid that outlined the code necessary to use a connection manager’s connection string in a SqlConnection in a script task.  The problem that I ran into was that this technique works only if the connection manager utilizes an ADO connection, which I was not.  Unfortunately I was using an OLEDB connection manager and was not permitted to change this to ADO.

After some work I discovered that an OLEDB connection manager still supports the ConnectionString property which provides a “;” delimited connection string.  The reason that a SqlConnection does not support a direct assignment to the ConnectionString property is because of how the OLEDB connection string is formed.  I was able to use the Split method to parse the connection string and successfully use the parsed string for my connection string.  The VB and C# code is below:

Visual Basic

Dim cm As ConnectionManager
cm
= Dts.Connections(“dbs”)

Dim str() As String = cm.ConnectionString.ToString.Split(CChar(“;”))
Dim cnstr As New StringBuilder
cnstr.Append
(str(0).Replace(“Data Source”, “Server”) + “;” + str(1).ToString + “;” + str(3).ToString)
MessageBox.Show(cnstr.ToString)

Try
Dim cn As New SqlConnection(cnstr.ToString)

cn.open()

Catch ex As Exception
MessageBox.Show
(ex.Message.ToString)
End Try

C#
ConnectionManager cm;
cm = Dts.Connections[“dbs”];

string[] str = cm.ConnectionString.ToString().Split(Convert.ToChar(“;”));
StringBuilder cnstr = new StringBuilder();
cnstr.Append(str[0].Replace(“Data Source”, “Server”) + “;” + str[1].ToString() + “;” + str[3].ToString());
MessageBox.Show(cnstr.ToString());

try
{
SqlConnection cn
= new SqlConnection(cnstr.ToString());

cn.open();

}
catch (Exception ex)
{
MessageBox.Show
(ex.Message.ToString());
}

Dts.TaskResult = (int)ScriptResults.Success;

 

My sample package includes two script tasks, one VB and the other C#, that include several code examples including ADO connection manager aissigments.  The package can be downloaded here.

Comments ( 2 )

  1. ReplyMark D.

    David, thanks for this post. It worked perfectly for me. Best regards.

  2. ReplyMark D.

    Thanks for this post David! It worked perfectly for me.

Leave a Reply


Hit Counter provided by Sign Holders