Understanding Scripting Tasks

You can use the Script Task in Control Flow. If you know ActiveX Script Task in SQL Server 2000 Data Transformation Services (DTS), you can regard Script Task as the next version of ActiveX Script Task. Script Task is now integrated with Microsoft .NET Framework to offer you the powerful features .NET Framework provides.

Figure 6-1 shows a basic programming model of the Script Task. When you create a new Script task in Control Flow, SSIS Designer generates a skeleton code of the ScriptMain class as well as of the Main method. All you have to do is write business logic inside the ScriptMain.Main method, using VSA and Code Editor. When the containing package is executed, the SSIS engine calls this Main method at first. You can also create your own method in the ScriptMain class and call it from the Main method, or you can create your own custom classes and modules.

Figure 6-1: Script Task programming model

Note 

As you can see in the skeleton code, you need to use Visual Basic .NET to implement a Script task. Although .NET Framework itself provides several kinds of programming languages, such as Visual C# .NET, Visual Basic .NET is the only option for the current version of SSIS.

The script often needs to work with a Data Transformation Services (DTS) global object. The DTS object provides a way to access SSIS built-in capabilities such as variables, event messages, and logs. You can use objects defined in external .NET assemblies. For example, if you would like to inspect the content of an XML file, add reference to System.Xml.dll and parse the file by using the XmlDocument class. You can also use your custom libraries or COM libraries in a Script task.

The rest of this section will show you how the Script task programming model that was discussed previously works and how you can implement custom code through the model. You will also learn the basic features of VSA and Code Editor through exercises.

More Info 

Because this book focuses on the features of SSIS 2005, the exercise doesn't provide much information about the Visual Basic .NET language or .NET libraries. If you need detailed information about them, please refer to the MSDN Web site at http://msdn2.microsoft.com/.