December 28, 2011

SSIS 2012: Parameters and Variables, what is the difference?

Note: The information on this post is based SSIS 2012 RC0

This is a question I ran across recently while browsing the SSIS 2012 pre-lease forum. While there may be multiple ways to look at the differences and similarities among them, I thought I would give it a shot and share some of my observations. IMAG0833
If you are familiar with earlier versions of SSIS, it is worth noting that the concept and usability of variables have not changed much in the newer version. on the other hand, it  is also important to point that parameters are one of the new features introduced in SSIS 2012 – available when using the new project deployment model - that promises to address some of the shortcomings of package configurations – which are still available.

Scope

Parameters can be defined at 2 different levels:
Project. These type of parameters are available to all packages within the SSIS project. Think about them as global parameters. They come handy as it makes really easy to share a given value, such as the path to a file share or the name of a server, across all packages in a project.
         Package. These type of parameters are meant to affect only the package on which they were defined.

Variables are richer in scope as they can be scoped at the package, container, task or event handler level.

Execution Time behavior


The value of a parameter can’t change within the execution instance of a package. That means, its value remains the same for the entire execution of the package. Honestly, I don’t envision this being an issue in most cases.
On the other hand, the value of variables can change during the execution of the package, which make them suitable for scenarios where, for example,  looping, conditional or any other type of logic where the values of the variable must change during the execution of the package.

Consumption and Usage


Parameters are applied via expressions on the properties which are intended to be parameterized. The value of a parameter can be set at development time, after the package is deployed to the SSIS server (via SSMS) or at run time (SSMS or SS agent job) and seem the way to go to affect package execution imagewithout having to modify the package. At design time, you can quickly get access to the “parameterize” window by right clicking on a task, container or connection manager, which saves few click when compared to using the expression property of the object. Additionally, parameter values can be set via execute package task, when a package is called from another package.

The value of a variable can be set in many ways within a package and its value can be further referenced by other expressions, containers, tasks or components.
Note: Expressions are not new to this version of SSIS and something you should put in your short list of things to learn if you are serious about SSIS.

Data types


When compared to variables, parameters do not support the following data types: Char, DBNull, Object.

Visual Studio Configuration Manager


SNAGHTML1ac6e626One of the nicest improvements I have seen in  the RC0 build is the ability to bind parameters to the design time configurations offered by SQL Server Database Tools (formerly known as BIDS), which comes very handy when designing and  and debugging packages in Visual Studio in SQL Server Data Tools.



Conclusion


Parameters are the new kids on the block, and it is clear they are being treated as 1st class citizen within the new project deployment model. Although variables and parameters  have some similarities, trying to find out which one is better may not be the right thing to do. In my opinion, it is more a matter of understanding their capabilities and  choosing the right tool for the job.
I am pretty sure there may be more aspects to parameters and variables  than the ones I listed here, so feel free to leave your feedback in the comments below with anything I may missed.

2 comments:

  1. Nice post, Raf! It appears parameters will be very welcome in my future projects!

    JamesNT

    ReplyDelete

Your comments are always welcome
Thanks for your feedback!