CODEWITHSUNDEEP
sqlsql-serverssis
Liam Domingo
Liam Domingo

Reputation: 263

How to set variable from connectionstring ssis

I have question regarding ssis packages. I want to populate a variable dynamically when I run my ssis package. The variable should be the connection string of the current step/task that is being executed. For example if I have an ssis package that has two steps, the first step's connection is Server1DB1 and the second step's connections is Server2DB2. When I run the package while the first step is busy executing the variable should be Server1DB1 and once it's done and step 2 is executing the variable should be Server2DB2. I have tried finding the answer but all articles speak about changing the connection based on the variable and not vice versa. Any help will be much appreciated. Thanking you in advance.

Upvotes: 0

Views: 4568

Answers (1)

Ben Gribaudo
Ben Gribaudo

Reputation: 5147

In a Script Task, fetch the ConnectionManager for the appropriate connection from Dts.Connections and then assign its ConnectionString property to a ReadWrite variable.

If your ConnectionString is set by an expression, you'll need to have the connection manager acquire the connection in order for the ConnectionString property to be updated with the expression's current value.

For example:

    public void Main()
    {
        // Where "MyOLEDBConn" is the connection's name
        var connectionManager = Dts.Connections["MyOLEDBConn"];

        // AcquireConnection/ReleaseConnection is unnecessary if ConnectionString isn't set by an expression
        var rawConnection = connectionManager.AcquireConnection(Dts.Transaction);

        // "User::ConnectionString" is configured as a ReadWrite variable for the Script Task
        Dts.Variables["User::ConnectionString"].Value = connectionManager.ConnectionString; 

        connectionManager.ReleaseConnection(rawConnection);

        Dts.TaskResult = (int)ScriptResults.Success;
    }

The task fetches the current value of the connection string, based on its expression, so it needs to be executed at a point where the expression's value is the same as it was when the task being logged acquired its connection. The task could possibly be placed directly before or after each step you want to log or (better yet) in a pre- or post-execute event handler.

An Alternative

Another approach would be to use an Expression Task to configure the connection string and assign it to a variable. Both the connection (via an expression) and your logging process could use this variable, bypassing the need to fetch the connection string from the ConnectionManager. This would eliminate the need for the above Script Task.

Upvotes: 1

Related Questions