1186
Part IX: Business Intelligence
■ (^) Name: The name assigned to your variable.
■ Scope: Variable scope used to be determined by the selected object in the con-
trol fl ow at the moment of creation, but now all variables default to being at the
Package level — an improvement. There is also now a Move Variable button that
changes the scope of a variable if necessary.
■ Datatype: The SSIS datatype. For a comprehensive look at SSIS datatypes as well as
a discussion on equivalency between SSIS datatypes and SQL Server datatypes see
http://msdn.microsoft.com/en-us/library/ms141036.aspx.
■ (^) Value: A default value that can easily be overwritten by a number of meth-
ods. Variables are read/write, whereas Parameters are read-only during package
execution.
The other buttons available in the variables window include a Move Variable button used
for changing a variable’s scope (new with SQL Server 2012), a Delete Variable button and the
Grid Options button, which enables you to see all the properties of the variables, including
seeing all system variables.
Variables can and are used to determine values during a package’s execution. The methods
to change the value of a Variable are as follows:
■ Through the result set of an Execute SQL task
■ (^) Through the confi guration of For and ForEach Loop Containers
■ Through Script Tasks and Components
■ (^) With an Expression, which can include a Parameter reference and often does
■ With a package using Package Deployment Model, with the Set parameter in the
DTEXEC command line interface
Using The SSIS Expression Language
You used a simple expression in the example to create the Modifi edDate column in the data
fl ow. This expression was written using the SSIS Expression Language, which you can use
throughout the SSIS environment anywhere a value must be formulaically evaluated. Some
of the key characteristics of the SSIS Expression Language include the following:
■ Parameters and Variables are referred to by prefi xing them with an @, making
@[User::foo] the fully qualifi ed reference to the user variable foo, and
@[$Project::foo] the reference to the Project Parameter foo. Columns are
referred to by their name and can be qualifi ed by their source name, making
[BikesNSuch].[Name]the fully qualifi ed reference to the Name column read from
the BikesNSuch connection manager.
■ Operators are C-like, including == (double equal signs) for equality tests, prefi x of
an exclamation mark for not (for example, !> and !=), && for logical AND, || for
logical OR, and? for conditional expressions (think IIf() function). For example,
c52.indd 1186c52.indd 1186 7/31/2012 10:29:30 AM7/31/2012 10:29:30 AM
http://www.it-ebooks.info