How to change a connection string in a DTS package at runtime from a .NET application?

Question

I am trying to run a dts package from a C# application. I need to dynamically change its connection string, though. I had a look on the web and found that I should use the Variables property of the Package object. Thing is, my Visual Studio 2010 does not show this Variables attribute for the package. The namespaces I am using are

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

What am I supposed to do?

Answer 1

Do you truly mean DTS (SQL2000) or its newer sibling SSIS? In any case, I had a similar problem where I had to specify multiple dynamic connections to a package. The way I solved it was this:

From the caller: - Write to a SQL table two columns: - The "Key" column was the particular connection to target - The "Value" column was the new connection value

Inside the package: - Use a SQL Task to read the key/value pair into @User:: variables - Use a Script task to get a handle to the running instance of itself --> Assign connection strings thru this handle

DTS Example:

' Get a reference to self (the DTS package currently running)
Set oPkg = DTSGlobalVariables.Parent

' Get connection to Input File - Set Path
Set oCn = oPkg.Connections.Item ( "MasterFileIn" )
oCn.DataSource = workAreaPath & "MasterFile.txt"

SSIS doesn't allow access to the active package object, but you might be able to set the connections directly. In your ScriptMain.cs file:

ConnectionManager cm = Dts.Connections["MasterFileIn"];
cm.ConnectionString = Dts.Variables["DataSource"].Value;