Get connection string in SSIS Script Task

ssis

In my SSIS package I have a package level connection manager named LAMP that I now want to access in my Script Task. I tried to use this to get the connection string:

var conn = Dts.Connections["LAMP"].AcquireConnection(Dts.Transaction) as string;

but that comes back with an empty string. Is it possible to pull a package level connection string?

It's an OLEDB connection manager, using the Native OLE DB\SQL Server Native Client 11.0 provider.

Best Answer

Frankly, you are doing it backwards. Create a variable to store the connection string. Then assign the connection string as a ConnectionString expression to the Lamp connection manager while also reading the connection string variable from the script task editor. The following steps should help:

  1. Create a variable call ConnectionStringLamp
  2. Input the connection string from the connection manager into the variable
  3. Click on the connection manager for Lamp. In the Properties window (bottom right), click on the ellipses (...) for Expressions
  4. Select ConnectionString and then assign the variable ConnectionStringLamp
  5. Validate it in the Expression builder to make sure the value populates
  6. In the script task editor, make sure you select the ConnectionStringLamp as a read-only (or read-write if you plan to change it).

  7. In your code:

    string strConnectionStringLamp = (string) Dts.Variables["ConnectionStringLamp"].Value;

Related Topic