Introduction to 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).

»


See All Articles by Columnist

Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles