Chapter 12. The Venerable Variable

In This Chapter

• Important Variable ConceptsProperties, Scope, Type, and Namespace

• System Variables

• Creating and Using Variables

• Execution Time Behavior

• Variable Tips and Tricks

"IT'S REALLY EASY IF YOU'RE IGNORANT."

JAMES HOWEY

In each succeeding version of DTS, variables have taken on a more important and central role in packages. This is certainly true of Integration Services (IS). In IS, variables have become absolutely essential. Variables are the primary method of communication between all components and make it possible to create packages that are more dynamic and configurable. This chapter discusses important concepts about variables and how to use them.

A common guideline in any traditional coding or development environment, "Hard-coding strings is bad," holds true for IS as well. Specifying the location of a file or the name of a table by directly typing it into a property should be avoided because it's difficult to manage and update packages written that way. Variables should be used extensively within the package for all settings. Following are some examples of how components use variables:

• Tasks use variables to store results that can be used in other components.

• Foreach Enumerators use variables to store the values of the current item being enumerated.

• Source adapters use variables to specify which table to reference.

• Variables are the primary method of dynamically building settings in expressions.

• The Script Task can use variables as input parameters.

• The SQL Task uses variables as parameters in queries and stored procedures.

• Event handlers have system variables that hold the value of the event parameters.

• The For Loop uses variables to determine when the loop should terminate.

Because variables have an expanded role and set of features, have grown in importance, and come in different flavors, this chapter is dedicated to thoroughly describing them.

So, what is an Integration Services variable? Simply put, IS variables are objects that store values and that other objects such as tasks and transforms use to store data temporarily.

Note

Variables were known as "Global Variables" in DTS. The reason Microsoft dropped the "Global" moniker is because variables in IS have scope and are not always visible to the entire package. Although you can still create variables at the package level (global) in IS, variables can now be created at different scopes throughout the package on containers.