SSIS Package Configuration - Use connection string

Question

In my SSIS package in Visual Studio 2017 I've created two package configurations. One for dev and one for prod. All I put into the package is the connection string.

I'm confused now on how I actually use that. When I double-click on one of my sql tasks for example, it's still showing the original connection string. How do I toggle between the two packages?

Also, for my script task, how do I access that connection string?

Abhishek Abhishek · Accepted Answer · 2018-07-09T07:24:35

First Part:

The connection properties and the values that you are seeing are static in nature which you have provided while developing your SSIS package. The configurations in your config file will only come to use when you actually run your package and then choose which configuration to use so that the package would then dynamically choose the configuration values from your dtsconfig file.

Read about Execute Package Utility here and dtexec here on how to use package configurations.

Alternatively if you want to run your SSIS packages using scheduled Sql Agent Jobs then the steps are mentioned here.

Second Part:

Here is a sample code snippet (C#) which you could use in your script task to get the connection manager information -

        public void Main()
        {
            ConnectionManager cm;
            cm = Dts.Connections["AdventureWorks"];

            MessageBox.Show(cm.ConnectionString, "OLEDB Connection");

            string myFlatFileConnection =
                Dts.Connections["Flat File Connection Manager"].AcquireConnection(Dts.Transaction)
                as string;
            MessageBox.Show(myFlatFileConnection, "Flat File Connection");

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

SSIS Package Configuration - Use connection string

1 Answers