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.
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
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
without 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
Visual Studio Configuration Manager
One 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 Conclusion
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.
Nice post, Raf! It appears parameters will be very welcome in my future projects!
ReplyDeleteJamesNT
I am glad you liked it
ReplyDeleteHi Rafael, I am working on SSIS ...in that I have many hard-coded values in child packages which i want to replace. I tried using variables but for one hard-coded value, I created one variable each in parent and child package and used execute sql task tool,created parent package variable via package configuration( in parent and child package) for passing the value. All this I have done for one hard-coded value. :(
ReplyDeleteIs there any easy way which I can use?
Guide me...
Aakash
Hey Rafael...thanks for the post. I ran into an issue where it doesn't look like my parameters are being used appropriately. I'm setting a project parameter in a stored proc via set_execution_parameter_value. I'm then using it in a SQL query to get another value once the master package starts running. I put that value into a result set using a User:: variable. I then bind both the User::variable and the project parameter to the child packages via parameter binding on the Execute Package task. However, when I run the master package, the values aren't seem to be getting passed. What's the best way to troubleshoot this for deployed packages? When I run in debug mode with my Project parameter set, it seems to work fine but in runtime, it doesn't, which makes me think that I don't have all of the "connections" between the Project parameter, user variables and child packages set appropriately. Thanks for any insight you can offer.
ReplyDeleteHi Angela, thanks for your comment. Are you running set_execution_parameter value inside of a package or while running the package via T-SQL? if it's the former, I don't think it will work. Parameter are meant to be set at the beginning of the execution not while the package is in progress. For the later I would use variables. You can troubleshoot at the runtime by looking at the SSIS builtin reports. There is one that will give you the parameter values being used at run time. I hope it helps
ReplyDeleteThanks Rafael...I think I figured it out!! I'm calling set_execution_parameter_value via T-SQL in a stored procedure. I finally figured out that I could watch the variables in debug mode and make sure that what I thought was being passed is being passed. Debug mode can fool you if you hard-code any values in places where they'll always work. Okay, so the issue was that through parameter binding in the Execute Package task, I was passing the value from the Parent package to the Child package's User variable...when, in fact, I should be passing the Parent package value to the Child package's *parameter*. I didn't have any child package parameters defined. Once I defined a child package parameter and set the User variable's expression to be the value of the child package parameter (@$Package:ParameterName), it worked! The moral of the story is values seem to only be passed from Parent to Child by passing from Parent package parameter/variable --> Child package parameter --> Child variable when using the Execute Task parameter binding feature. If someone can get it to work differently, I'd love to hear how. This also adds additional information to Melissa's post (http://www.sqlchick.com/entries/2013/9/15/getting-started-with-parameters-variables-configurations-in.html). A sort-of circular reference can be added in the Package "box" going from the User variable to the Package parameter to signify the Parent-Child package relationship.
DeleteArgh! I thought I had it working but it's not. I changed all of the references from User:Variable to $[Package::Parameter] and I changed the Parameter binding in the Execute Package task so that the child packages parameter bind's to the master package (parent's) user variable. The child parameter is set to 0 in each package and that's what's being used in all Source tasks that have expressions that use the Child parameter. Any insights? Thanks. :-)
ReplyDeleteVery nice article. Thanks!
ReplyDeleteRafael, very nice articel.. but i have one question about executing another package in ssis 2012. well, let us tell there are two project in different solution, those are using project parameter as connection. in a package project one, there is one package execution task called second package in project two. and because of project parameter conn, the package failed. the cause is the child package didn't find connection that only defined as project connection. any idea , advice, or suggestion ? thank you.
ReplyDelete