Database Reference
In-Depth Information
Source Control
Ask any good developer for their short list of required project elements, and source
control will almost always be near the top of the list. Because any SSIS project really is
software development—albeit in a mostly graphical environment— the same consider-
ation must be made by ETL developers as well. Being that the storage for an SSIS
package is simply an XML file, it's not difficult to add SSIS packages to most any ex-
isting source control system.
To some, the Script task and Script component have the appearance of residing out-
side the SSIS package—after all, both of these are managed through what appears to be
a separate Visual Studio project. This thinking sometimes brings up the question of
how to integrate SSIS script logic into source control. The easy answer is that there is
no requirement above and beyond source controlling the package itself. Because all of
the code is stored inline within the package XML, there is no need to separately track
in source control the code within instances of the Script task or Script component.
Scripting Design Patterns
As a born-and-raised Southerner, I was always taught that there's more than one way to
skin a cat. Although I've never attempted this particular exercise, I can confirm that for
any given problem (in life, as well as in SSIS) there may be dozens or perhaps even
hundreds of correct solutions.
Because of the highly flexible nature of scripting solutions, it's not possible to pre-
dict every possible permutation of logic that could find its way into SSIS code.
However, as Integration Services has evolved, some commonly used design patterns
have emerged. In this section, I'll look at some of these patterns.
Connection Managers and Scripting
Connection managers are built into SQL Server Integration Services as a modular way
to reuse connections to databases, data files, and other sources of information. Most
everyone with even a little experience using SSIS is aware of connection managers and
how they relate to conventional components such as OLE DB Source/Destination and
Flat File Source/Destination, as well as tasks such as the FTP task and the Execute
SQL task. You can instantiate a connection object once as a package-level entity (as
shown in
Figure 3-7
) and use it throughout the remainder of the package.
Search WWH ::
Custom Search