Connection Managers using Parameters

This document explains how to use parameters with connection managers, so that we can build a ETL package dynamic database connections. In this example, we will see how we can use Server Name, Login Name, Password and default database.

Step 1: Set up Project Parameters for the Connection Manager. From the Solution Explorer Select the Project and Project.params, double click to open or select open from the menu.

Step 2: Create three variables as shown in the picture

Step 3: Now Right click on the connection from the connection managers tab that you want to change, right click and from the menu choose Parameterize

Step 4: A dialog box opens for parameters and select the property drop down to select the parameter you want to change, in this case I am going to choose the server, and then use an existing parameter to utilize my previously created variable and then click OK. 

Step 5: Do the same for other variables.

Parameters

Parameters are objects within the SSIS packages whose values can be predetermined or easily configured while executing the package. These parameters can be used to configure run time behavior of package executions with the new server based storage model of SSIS packages; parameters are an efficient way to configure run time values of package or project properties. (MSDN Blogs)

Two Types of Parameters

• Package Parameters
• Project Parameters

A package parameter would be scoped and be available for the respective package execution while a project parameter would be available for the execution for any package within the project.

Creating Project Parameters 

Step 1: From the solution explorer double click to open the Project Parameters tab

Step 2: Click on the Add Parameter icon on the tool bar to create your variable

Use the variable at script task

Step 1: Create a script task at Control Flow

Step 2: Double Click on the Script Task and Choose "Read only variable" if you are not planning to change the variable at run time or choose "Read Write Variables" if you are planning the modify the variable content during the run time.

Step 3: From the list choose the variable we just created $Project::ServerName

Step 4: Click on Edit script and add the following to display the value of the variable

Step 5: When you execute, you may able to see the Message Box with whatever the server name