Database Reference
In-Depth Information
CHAPTER 3
Scripting Patterns
As shown throughout this topic, SQL Server Integration Services (SSIS) is a multifa-
ceted product with many native capabilities that can handle even the most difficult of
data challenges. With highly flexible transformations, such as the Lookup, Conditional
Split, Derived Column, and Merge Join, the data flow is well-equipped to perform a lim-
itless number of transformations to in-flight data. On the control flow side, tools includ-
ing the File System task, the For Each Loop (and its cousin, the For Loop), the File Sys-
tem task, and the Data Profiling task provide critical services to support fundamental
ETL operations. Right out of the box, you get a toolbox that's full of flexible and power-
ful objects.
However, even the most casual ETL developer will eventually encounter scenarios
that require more flexibility than what is afforded in the native components. Dealing
with data movement and transformation is often ugly and unpredictable, and requires a
level of extensibility that would be difficult to build into general-purpose tools. For-
tunately, there is a solution for these uncommon ETL needs: custom .NET scripting.
The Toolset
SQL Server Integration Services has the capability to build very powerful ETL logic dir-
ectly into your SSIS packages. Through Visual Studio and its various niceties (familiar
development environment, IntelliSense, and project-based development, among many
others), the burden of embedding custom logic in your ETL processes is made signific-
antly easier.
Search WWH ::




Custom Search