SSIS: An Overview of Variables

Enabling data-sharing between tasks within a package.

Often when performing multiple complex tasks within a package, data created from one tasks must be accessed either for testing or further manipulation by other tasks. Variables provide the best method for providing this kind of cross-collaboration.

Variables may provide system data such as those commonly used within software/web applications (DateTime, Culture, etc.) or custom user data that is generated either before or during the execution of the package.

As in traditional programming, correctly adhering to the concept of scope is vital to correctly using variables. Since there are no “global” variables, the highest level possible is at the package level. This means that all tasks within that package may access that variable’s value.

When naming variables, be careful not to resuse names of other variables at other scope level since the highest level instance would hide the lower level variables with the same name.

To illustrate using variables, I’ll begin with a new SSSIS project and add new new variable, leaving its default scope as “Package.”

1-variables-overview

Next, I’ll add a Sequence Container Control Flow task and with it selected, a another variable which has its scope “Sequence Container.”
Notice when the new tasks isn’t selected, its variable is hidden and only the PackageLevel variable is displayed.
2-variables-overview

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s