SSIS Script Task

SQL Server 2008 Integration Services contains an assortment of predefined Control Flow tasks designed to carry out specific types of actions. Collectively, they cover a wide range of scenarios; however, their specialized nature sometimes turns out to be overly restrictive. This article explains how to accommodate these custom requirements by employing the considerably more flexible Script Task.

SQL Server 2008 Integration Services contains a diverse assortment of predefined Control Flow tasks, which are designed to carry out specific types of actions. While collectively they cover a wide range of scenarios involving data extraction, transformation, and loading, their specialized nature sometimes turns out to be overly restrictive. In situations like these, it is usually possible to accommodate custom requirements by employing the considerably more flexible Script Task. In this article, we will cover its most relevant features (it is important to note that its purpose is quite different from the Data Flow-based Script Component, whose characteristics we are also planning to present on this forum).

It is hard to overstate the flexibility of the Script Task, considering that boundaries of its capabilities are defined primarily by your ingenuity, skills, and .NET programming model (starting with SQL Server 2008 Integration Services, it became possible to use Microsoft Visual C#, in addition to Microsoft Visual Basic available in its predecessor). The task operates essentially as a wrapper of managed code with access to SSIS-specific objects, including their methods and properties, interacting with a parent container (and other SSIS entities) through arbitrarily chosen System and User variables. Its modifications are handled using Visual Studio Tools for Applications (VSTA) replacing Visual Studio for Applications (VSA) present in earlier versions (which, incidentally, was the primary obstacle to providing support for Visual C#). The VSTA interface offers visual enhancements standard in practically every current software development environment such as color-coding or IntelliSense as well as debugging functionality including breakpoints (which integrate with breakpoint indicators of SSIS tasks and containers) or Immediate and Output windows. Furthermore, it simplifies referencing Dynamic Linked Libraries as well as making it possible to directly reference Web services and COM modules, eliminating the need for the creation of proxy classes and interop assemblies or for copying them into Global Assembly Cache and Microsoft.NETFramework folders (which used to be the case when working with VSA). The resulting code is precompiled into binary format (VSA was more flexible in this regard, giving you an option to postpone compilation until execution), effectively shortening total runtime of the package (although at the cost of its overall size).

In order to identify the most relevant characteristics of Script Task, let’s examine in more detail its interface exposed in the Business Intelligence Development Studio. Create a new project based on the Integration Services template, add the task to its Designer window (by dragging its icon from the Toolbox), and display its Editor dialog box (by selecting Edit entry from its context sensitive menu), which is divided into three sections:

• Script section – containing the following elements:

• • ScriptLanguage textbox – designates the programming language (Microsoft Visual Basic 2008 or Microsoft Visual C# 2008) in which code contained within the task is written. Make sure to choose the intended entry before you activate the Visual Studio Tools for Applications interface (by clicking on the Edit Script… command button), since at that point, you will no longer be able to alter your selection (this action triggers auto generation of the ScriptMain class based on a built-in template using the language of your choosing).
  • EntryPoint textbox – identifies a method (which must be defined as part of the ScriptMain class in the VSTA-based project) that is invoked when the Script Task executes (set by default to Main)
  • ReadOnlyVariables and ReadWriteVariables textboxes – determines which SSIS variables will be accessible from within the script by listing their names (as comma-separated entries in the format namespace::variable name). While it is possible to type them in, the most straightforward (and the least error prone – since they are case sensitive) approach involves pointing them out directly in the Select Variables dialog box accessible via the ellipsis (...) command button located next to each textbox. Another approach to specifying SSIS variables that can be either viewed or modified within a Script Task leverages LockForRead and GetVariables methods of VariableDispenser object (we will explore it in more detail in our future articles), however it is important to realize that these methods are mutually exclusive (an attempt to reference the same variable using both will result in an error).
  • Edit Script… command button – triggers display of Microsoft Visual Studio Tools for Applications 2.0 Integrated Development Environment with the majority of its desktop estate occupied by the window containing the task code. In addition, you will also find Properties and Project Explorer windows, where the latter references the newly created Script Task via automatically generated identifier (which guarantees its uniqueness and therefore should not be changed). Effectively, content of the task constitutes a separate project, with its own set of properties (independent from characteristics of the SSIS project it is part of) accessible via its context sensitive menu and displayed in the tabbed window divided into the following sections:

• • • Application – designates properties of the assembly (some of which, such as output file, name, and root namespace are derived from the auto generated identifier of the script task). In general, settings on this page are intended to create independent assemblies via a full-fledged edition of Visual Studio and therefore are not relevant in the context of our presentation (as a matter of fact, most of them are grayed out because of their read-only status), although you have the ability to customize Assembly Information (including such parameters as Title, Description, Company, Product, Copyright, Trademark, Assembly Version, File Version, GUID, or Neutral Language) as well as Make assembly COM-Visible.
    • Compile – allows you to modify the Build output path (by default pointing to bin subfolder), Compile option (such as Option explicit, Option strict, Option compare, and Option infer), a number of Warning configurations, settings such as Disable all warnings, Treat all warnings as errors, Generate XML documentation file, or Register for COM interop, as well as a number of Advanced Compile Options (for example, defining DEBUG, TRACE, or custom constants).
    • Debug – provides the ability to assign Command line arguments and Working directory for debug start options.
    • References – likely the most helpful feature available via the Properties window, considerably simplifies adding assembly references to your projects (replacing cumbersome process implemented in previous version of Visual Studio for Applications of SQL Server 2005 Integration Services) as well as identifying and removing unused ones.
    • Resources – facilitates management of project resources, such as strings or bitmap, icon, audio, and text files. This functionality is intended for development of standalone programs and is not applicable here.
    • Settings – defines project’s application settings and similar to the Resources page, contains entries pertinent to development of independent applications.
    • Signing – provides the ability to sign assemblies (which requires a strong name key file), which is not relevant in this context.

• General section – containing the following entries:

• • Name – allows customizing the name of the Script Task (which happens to be its default) in order to improve readability of your package. Note that its value must be unique within a package.
  • Description – intended for additional information helping you make packages self-documenting.

• Expressions section – provides the ability to assign values of Script Task properties , by dynamically evaluating associated expressions (rather than specifying their values directly).