Professional SQL Server™ 2005 Integration Services

7.1. 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 ...