Chapter 7: Scripting in SSIS

Data Transformation Services has always had the ability to execute custom script tasks. This feature has been greatly enhanced in SQL Server Integration Services. Now you have the ability to develop scripts using Visual Basic.NET, a full-fledged programming language that has many improvements over interpreted scripting languages. In this chapter, we will show you the different scripting options, such as controlling execution flow and performing custom transformations, and demonstrate examples of each.

Scripting Overview

The scripting abilities of SSIS have come a long way from their predecessors in DTS for SQL Server 7.0 and 2000. Let's take a look at the possible scripting options in SSIS 2005.

• ActiveX Script Task: This task has been with DTS since SQL Server 7.0. It allows the execution of a script written in an ActiveX scripting language, such as VBScript. This task was included for backward compatibility with previous DTS packages and will be removed in the next version of SQL Server, so now is the time to upgrade to the newer components. This task will not be discussed further in this chapter.

• Script Task: This task is the replacement for the ActiveX script task and is used primarily for controlling package execution. It allows the execution of a script written in Visual Basic.NET. Unfortunately, Visual Basic.NET is the only language you can use to write a script using this task. From your script, however, you can use assemblies written in other .NET languages such as C# or COM components through COM-Interop.

• Script Component: This component allows for the creation of a totally custom transformation. This component is useful when the built-in transformations are not powerful or flexible enough for your needs.

• Expression Language: SQL Server Integration Services includes an expression language that allows you to set variable values and perform other operations.

There are three types of scripting that you can perform in SSIS: expressions, Control Flow scripting, and Data Flow scripting.

Expressions are written with the built-in expression language to define property values and provide the logic used to execute certain tasks and transformations. The Derived Column transformation, for example, uses the expression language to determine the value of a derived column.

Control flow scripting is achieved using the Script task. You can control the flow of package execution using a Script task. For example, perhaps you want a notification sent to one person when the package completes during normal business hours but to another person during off-hours. You could use a script task to evaluate the time and send the appropriate notification.

Data flow scripting is used for custom transformations. The Script Component accomplishes this task. An example of using this component would be evaluating the values from a source table and converting them to the expected values in a destination table. Suppose you are importing data from a mainframe, and the original system doesn't have much in the way of data validation, so you must make sure the data is valid before importing it. You may even want to correct the data in some situations. These are perfect tasks for the Script Component.