Monday, June 8, 2015

SQL Server Brain Basher of the Week #014 - SSIS Variables and Parameters

We have been using variables inside Integration Services packages for maintaining temporary values for various computations and passing external values to the package when executing. With SQL Server 2012, Parameters were introduced which is similar to variables and many still get confused with these two; what to be used and when and why they should be used, hence this week Brain Basher is based on Integration Services Variable and Parameters.

What is the difference between Variables and Parameters with Integration Services?

Variables are used to hold values temporarily for calculations and configurations. A common usage of variables is, maintaining one for Connection String of one of Connection Managers. Before SQL Server 2012, we were able to pass new values for these variables at the execution time using Configuration Files, however with SQL Server 2012 and later, it is not possible with Project-Level-Deployment.

Parameters are similar to variables. However,  they can be defined at Package Level, making them as Private to the package. They can also be defined at Project Level, making them as Global to the project. Once defined, parameters can be used for calculations and holding values for configurations. And values them can be passed at the execution time with Project Deployment Model.

The main difference between these two is, with modern deployment, variable is for internal calculations and not for getting external values at the execution time. Parameter can be used for assigning values to variables and value of it can be passed at the execution time.

No comments: