Parameterizing Connections and Variables in SSIS

Being on SSIS presentation recently, I've realized that a lot of people, who are working with SSIS for years, still do not know what "Parameterizing" is and how to do it.

SSIS has been changed a lot in SQL Server 2012, where Microsoft announced "Project Deployment Model". Since then you can deploy Project, and you can assign Parameters to that project, which can be passed to it for execution. Before that, developers used Configurations to supply values for internal variables and connections.


For this test I've created one database "Project" scoped connection "AdventureWorks2016", which has to be available for all packages within my project, and "Package" scoped variable "TestVariable":

Parameterizing Connection

1. Do a right click on "AdventureWorks2016" connection and "Parameterize..."

2. Then you'll see "Parameterize" window. There you can change name of your new parameter or even not create a new parameter, but assign an existing one. You can also choose which property of your connection you want to parameterize. By default it offers "ConnectionString".

 

3. After necessary changes are done, press "OK". Then you'll see little "Function" sign near your Connection.

4. To see your newly created parameter just double click on "Project.Params" in "Solution Explorer":

It will open new "Project.Params" window with our new parameter:

ParameterizingVariable

That procedure is even easier.
1. In your opened "Project.Params" window click "New" icon:

2. Rename it to "TestParameter", change type to "String", and assign "Test Value":

3. Then switch back to your "Package" window and click on "Expression Builder" button for "TestVariable" in the "Variable" window:

4. You'll have "Expression Builder" window opened:

5.  Expand "Variables and Parameters" section and "Drag-n-Drop" "TestParameter" to the Expression small window. Please note the difference between Variables and Parameters: Parameters have "$Project" signature.

6. Than click OK and we will see the parameterized value for our "TestVariable":

Note the "Function" sign assigned to the variable.


As you can see it is very easy to parameterize.