The Task Editor Expressions Tab
SSIS uses a concept of setting the value of most task properties to a dynamic expression that is
evaluated at runtime. This way, you can dynamically configure packages at runtime, replacing the
Dynamic Properties Task and scripting-based configuration of the legacy DTS object model. Common
to all the tasks is an Expressions tab in each of the editors that exposes the properties you can set
dynamically at runtime with an expression. The expression can be a constant value, an expression,
or an SSIS variable that is either a scalar constant or an expression. With this capability, you could
read a series of variables from a configuration file (these are discussed later) and then dynamically set
properties of any SSIS task. We provide many examples of using expressions throughout this book.
Chapter 5 is dedicated to variables and expressions, and explains how to use them in SSIS.
For a basic understanding of the Expressions tab within each task, click the ellipsis (…) button next
to the Expressions option in the Expressions tab of any of the Task Editors. This will take you to
the Property Expressions Editor, where you can set properties within a task — either directly by
providing the actual expression or indirectly by providing the variable that evaluates to the result of
an expression. To create a new one, select the property you wish to set from the Property column
and then type the expression into the Expression column. Optionally, you can also select the ellipsis
button in the Expression column to open Expression Builder, where you can create an expression
using a visual UI. You’ll see this capability in most of the SSIS tasks.
Looping and Sequence Tasks
First up in the SSIS Toolbox are three container tasks: For Loop, Foreach, and Sequence. These are
all Control Flow Tasks that simplify the process of repeated processing of a set of logic. In legacy
DTS, looping constructs were not intuitive. If you wanted this type of Control Flow logic, you had
to set properties on the tasks directly using code in the ActiveX Scripting Tasks. To achieve the same
thing in SSIS, you only need to add one of these containers to your Control Flow and define what is
being used to enumerate the loop. Again, these containers are covered in detail in Chapter 6, so they
are only briefly covered here.
Sc ript Task (.NET )
The Script Task enables you to access the Microsoft Visual Studio Tools for Applications (VSTA)
environment to write and execute scripts using the VB and C# languages. Using this task, you can
create additional logic that the canned SSIS tasks can’t accomplish.
Scripting now is almost a misnomer because the latest SSIS edition solidifies the connection to the
full .NET 4.0 libraries for both VB and C#. The latest addition to SSIS of the VSTA environment
and the Script Task, in general, also offer these extra functional advantages:
➤➤ A coding environment with the advantage of IntelliSense
➤➤ An integrated Visual Studio design environment within SSIS
➤➤ An easy-to-use methodology for passing parameters into the script
➤➤ The capability to add breakpoints to your code for testing and debugging purposes
➤➤ The automatic compiling of your script into binary format for increased speed