Scripting Patterns - SQL Server Integration Services Design Patterns - page 103

Unlike its predecessor Data Transformation Services (DTS), SQL Server Integra-
tion Services exposes the entire .NET runtime within its scripting tools. Gone is the re-
quirement to use only ActiveX scripts within ETL packages (although this capability
does still exist in SSIS, for those loyal to VBScript). With the introduction of the rich
scripting environments in SSIS, you now have the ability to access the same frame-
work features used in “real” software development. True object-oriented development,
events, proper error handling, and other capabilities are now fully accessible within
custom scripts in SSIS.
SQL Server Integration Services includes two different vehicles for leveraging
.NET code into your packages, each designed to allow different types of custom beha-
viors. The Script task, which resides in the control flow toolbox, is a broad, general-
purpose tool intended to perform support and administrative tasks. Within the data flow
toolbox, you'll find the Script component, a versatile yet precise data movement and
manipulation tool.
If you're new to scripting in SSIS, you might wonder why there are two different
script tools in SSIS. Beyond that, the critical design pattern will include a decision on
which of these tools to use in a given scenario. The short answer? It depends. As men-
tioned, the Script task is typically the better choice for operational behavior and is most
commonly used for operations affecting overall package flow (as opposed to data
movement). On the other hand, if your ETL needs require generating, consuming, or
manipulating rows of data, then the Script component is normally the better tool.
Although they have nearly identical interfaces, the Script task and Script compon-
ent differ greatly in their default design. As you explore each of these tools, you'll find
that there is a significant amount of code automatically added to the script project when
you introduce it to your work surface. As a tool designed for direct data interaction, the
Script component will include preconfigured code defining inputs and/or outputs to al-
low data to flow through the component. In contrast, the behaviors built into the Script
task have no facilities for either inputs or puts, further illustrating that this tool was
built for purposes other than direct data manipulation.
The Script task and Script component share many similarities. Both tools feature a
script designer that resembles the familiar Visual Studio development environment
used for mainstream software development. In both tools, you'll find the workspace or-
ganized into a virtual solution (displayed very similarly to the solution container found
in Visual Studio development) that may include multiple files and folders. Also com-
mon to both script tools is the ability to include external code or services within the vir-
tual solution. This capability allows you to leverage code that has already been written
and compiled elsewhere, whether it's a DLL file you include with the project or some
external resource such as a web service. The language behaviors in both tools will be