Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 14

Using the Expression Task

A new task introduced in this release of SQL Server is the Expression Task. Expression building is covered more in depth in Lesson 35 of this book, so this lesson focuses on using the Expression Task in the Control Flow.

In previous versions of SSIS, if you wanted to manipulate SSIS variables in a Control Flow, you would need to use a Script Task. This required some .NET programming knowledge and the package had to compile a script. Now you can skip the Script Task and use the new Expression Task.

The editor for the Expression Task is identical to the Expression Builder found in the Expression property of the Control Flow tasks and variable expressions. The common SSIS expression syntax is used in this task also. Figure 14-1 shows the Expression Task Editor. Notice that the title at the top is Expression Builder. One more difference in this version of SSIS is that the folder in the top left is titled Variables and Parameters. Parameters are a new addition in SQL Server 2012 and are covered in Lesson 33 of this book.

Figure 14-1

One of the common scenarios in which the Expression Task would come in handy is incrementing variables in a loop. Loops are covered in Lessons 42 and 43 of this book. Looping through a set of files to load into a database is a common use of SSIS. Auditing the number of files that run through the loop is also commonly ...