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

Once the language and variables are set, you're ready to open up the code editor.
When you click the Edit Script button, you'll be presented with the familiar Visual Stu-
dio development environment. If you have written code using this IDE in the past (to
develop Windows applications, web apps, etc.), you'll recognize many entities—the
Solution Explorer, the code window, the properties window, and many other common
components. Although the behaviors will be a bit different in this minimized version of
Visual Studio, the concepts remain the same.
The Script Component
Although both SSIS scripting tools have similar properties, they serve very different
roles. Unlike the Script task, which is intended mostly for administrative and opera-
tional programmability, the Script component is designed for the more traditional mov-
ing parts of ETL: retrieving data from a source, performing some manner of transform-
ation or validation on said data, and loading data to a destination.
Common uses of the script component include:
• Connecting to sources of data for which there is no native source com-
ponent
• Sending data to destinations that do not offer a native destination or are
structured differently than the typical tabular layout
• Performing advanced data manipulation that requires functionality not
offered with the built-in SSIS transformations
• Complex splitting, filtering, or aggregating of the in-pipeline data
The Script component is built with the versatility to behave in one of three modes:
transformation, source, or destination. When you introduce an instance of the Script
component into your data flow workspace, you'll be prompted to select a configura-
tion, as shown in Figure 3-6 . This is an important choice, since the selection you make
will determine which code template is used to initially configure the component. I'll
dig more into each of these roles momentarily.