SSIS: Put the current connectionstring in a variable

By | August 21, 2009

I’m creating a SSIS package that will call a console application, and as a argument i want pass the current connection string. As i’m using a shared XML config file over multiple packages that sets my connection, i am unable to let the xml config file fill a variable.

You can use a Script task to get the connection string directly from the connection manager into a variable:

public void Main()
        {
            ConnectionManager conMgr = Dts.Connections[“Miss”];
            //No connection string available, fail this step.
            if (String.IsNullOrEmpty(conMgr.ConnectionString))
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
            else
            { //Else fill the variable and return succes
                Dts.Variables[“ConnectieString”].Value = conMgr.ConnectionString;
                Dts.TaskResult = (int)ScriptResults.Success;
            }
        }