Introduction to SSIS 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