How to change the OLEDB connection information using Script Task?

Question

I've an OLEDB Connection, I want this connection to be changed dynamically in the script. I am getting the information of this OLEDB Connection using the following code:

ConnectionManager cm = Dts.Connections["VendorDB"];
        DbConnection conn = null;
        if (cm.CreationName == "OLEDB")
        {
            Wrap.IDTSConnectionManagerDatabaseParameters100 cmParams =
            cm.InnerObject as Wrap.IDTSConnectionManagerDatabaseParameters100;
            conn = cmParams.GetConnectionForSchema() as DbConnection;
        }
        else
        {
            conn = cm.AcquireConnection(null) as DbConnection;
        }

        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        MessageBox.Show(conn.ConnectionString.ToString());

I need to change this connection information (like I want to change the Initial Catalog etc.) and want this changed information to retain throughout the package. How to do so?

Answer 1

I would resolve this issue by using a combination of things

• First I would define an SSIS Variable as indicated here
• The I would make the connection string an expression as indicated here
• And change the variable via script as indicated here

The first step is relatively easy:

Open the Variables tab (right click inside control flow and click Variables) and add the variable you want (Maybe String for your connection string)

Make an expression using your newly defined variable. Use the code from the third link to change the value at runtime.

Dts.Variables("myVariable").Value = @"C:\Test2.txt";

Dts.TaskResult = ScriptResults.Success