Exam Prep 70-463: Variables and Parameters


This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012.  So far, we’ve covered:

Today we continue our series by discussing SSIS variables and parameters.

Intro to Variables

There are two kinds of variables in SSIS: user-defined and system.  User-defined variables are created by the developer (aka you) to store dynamic information or results during execution. The value of user-defined variables can be set and changed at any time during the lifetime of the variable.  They can be useful for making your packages more dynamic and reusable.  User-defined variables can be used to hold parameters to be passed into an Execute SQL Task or they can hold the results of that task.   The entire TSQL of that task can be held in a variable, for that matter.  Variables are also used in looping containers, such as the Foreach Loop Container, to hold rowsets or file names that need to be processed.

System variables are a set of pre-defined variables that represent specific properties within SSIS.  Unlike user-defined variables, the value of system variables are set by SSIS and cannot be changed.  Some examples of system variables are ContainerStartTime and StartTime, which hold the time the container or package started to run, respectively.  These can be useful for logging/auditing within your package.  Variables like ErrorCode and ErrorDescription are great for error handling (which you’ll always incorporate into your packages, right?).

Scope

Every variable will have a defined scope that will determine when and where a variable will be visible.  This scope and visibility has a lot to do with the hierarchy within SSIS.  A variable created within the package scope is considered global, because the package is at the root of the hierarchy.  You’ll be able to access that variable from anywhere in the package.  However, let’s say you create a Foreach Loop container to iterate through files in a directory.  You create a variable in that container to hold the current filename being processed.  That variable will be visible to the container and any task you create in that container.  However, it won’t be visible to a task outside of that container.  If you then add a task to that container, and create a variable in that task, that variable will only be visible to that specific task.

Variables vs. Parameters

The terms variable and parameter tend to be thrown about interchangeably.  They are, in fact, separate concepts with one big distinction:  variables are dynamic and can be changed during execution whereas parameters are passed in at execution time and are then static.  Parameters are intended to expose properties to the user/administrator at run time.  ConnectionStrings, instance and/or database names are commonly parameterized to aid in portability.  This way you can run a package against different environments without having to edit the package every time.

Additional Resources

For more information on variables and parameters, check out the following resources.


Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

One thought on “Exam Prep 70-463: Variables and Parameters