SSIS dynamic connection string

Question

I'm trying to make a dynamic connection to DB for all of my packages. I'm trying to achieve this by loading my user defined variables with a Script Component .

I know I can also use environment variables to do this, the problem is that not all of my packages will be scheduled and some of them I run with a web service or a procedure, so I can't use a scheduled job to do this, and using the @reference_id is a bit complicate to maintain.

I have no idea how can I loop the variables. I tried

foreach (Variable var in Variables)

But that's not the way ..
So I have two questions here:

How do I loop all the variables I passed?
How do I get the Variable name out of it ? (I don't see a name attribute on Variables.VarName.?)

E.G. The OLE DB source will return two rows:

conName | conString
 con1   |  someConnection
 con2   |  someConnection

And I have two user defined variables in my project -> con1 and con2 . So, iterate through my varaibles -> foreach(variable) , if var.name = row.conName , load with row.conString value .

@plaidDK It should be dynamic. The variable names can change, that's why I need to loop them all . — sagi
– sagi, Commented Sep 25, 2017 at 11:42
maybe this will help you: timmitchell.net/post/2015/04/20/… — SqlKindaGuy
– SqlKindaGuy, Commented Sep 25, 2017 at 11:45

sagi · Accepted Answer · 2017-09-25 14:36:09Z

1

Finally I've managed to do this. For those of you who will want to do the same, here is what I did :

public class ScriptMain : UserComponent
{

    Dictionary<string, string> AllCons = new Dictionary<string, string>();

    public override void PostExecute()
    {
        base.PostExecute();
        string connectionString;

        foreach (IDTSVariable100 obj in ReadWriteVariables)
        {
            if (AllCons.TryGetValue(obj.QualifiedName, out connectionString))
            {
                obj.Value = connectionString;
            }
        }
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        AllCons.Add("User::" + Row.ConName, Row.ConString);
    }
}

I used a Dictionary object to store the connection name and connection string on the Input0_ProcessInputRow section. Then, in the PostExecute, I've iterated through all of my User Defined Variables using an IDTSVariable100 Object. I then checked, if the connection name equals the variable, put the connection string in it.

Hope it helps someone.

answered Sep 25, 2017 at 14:36

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SqlKindaGuy Over a year ago

Glad you got it to work. You could do the same as in the link i posted. Where you set your object to a datatable :)

Collectives™ on Stack Overflow

SSIS dynamic connection string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related