SSIS Connection Manager not using the ConnectionString value

0
votes

I have a child package where the ConnectionString property of a Connection Manager is set by a Parent Package Variable Configuration. I set up a script task that brings up a message box with the value of the ConnectionString property right before the dataflow task.

`MessageBox.Show(Dts.Connections["CPU_*"].ConnectionString.ToString());`

When I run the parent package, the message box shows that the connection string is changing with every iteration, but in the dataflow it always draws the data from the same source.

I'm using SQL Server 2008 R2, the connection manager is an ADO.Net type, RetainSameConnection is set to False, and I've been researching this for days. Anybody have any ideas?

Update (2/23/2015): To make this stranger, when I look at the diagnostic logs, they tell me that when the new connections are being opened they are using the new connection strings.

1
sql-serverssissql-server-2008-r2connection-string

1 Answers

0
votes

I found an answer here Passing SSIS Connection String in parent variable works but package still validates against child design value.

I'm not sure about later versions yet, but certainly up to 2008R2 there is a bug when you pass a connection string into a child package. As you correctly point out the connection string IS passed, but either the connection is evaluated prior to the parent configuration or the connection string is updated from the design object after the parent configuration has been passed.

Either way it just doesn't work.

If, like me, you don't want to add an additional Script Task to all your packages you will need to do the following:

Parent Package

  • Lets assume we are using a OLE DB connection called MyDBConnection
  • Add a string variable to hold the connection string (MyConnection)

  • Add a C# Script Task (before the Package Task) with the line:

    Dts.Variables["User::MyConnection"].Value = Dts.Connections["MyDBConnection"].ConnectionString;

Child Package(s)

  • Add a string variable to hold the connection string (MyConnection)
  • Add a parent package configuration to pass the value of MyConnection
  • In the properties for the OLE DB connection add an expression to update the Connection String property from MyConnection