Execution Patterns - SQL Server Integration Services Design Patterns - page 83

velop in the .NET language of their choice, if this is the case. If they
found their way to SSIS via a role as a database administrator, I use
more T-SQL in Execute SQL tasks and OLE DB Source adapters when
developing the solution. If they have DTS, SSIS, or other ETL develop-
ment platform experience, I develop packages slightly differently to
match their comfort zones. Again, this is different for different enter-
prises.
• Enterprise requirements: I often encounter “best practices” at enter-
prises. I enclose the terms in quotations because, well, some of them
aren't actually best. They exist because something bad happened and
someone reacted. Sometimes the reactions make sense from an SSIS
point of view, sometimes they are security matters that vex the SSIS de-
velopers, and sometimes they just do not make good sense for anyone.
• Complexity: I do not like complex solutions. I tolerate them if they are
the only way to accomplish what needs to be done, but I strive to keep
solutions as simple as possible. Fewer moving parts means there is less
to break, less to troubleshoot, and less to maintain. That said, flexibility
and complexity are often proportional. That means highly flexible solu-
tions are likely to be complex solutions.
I write this here, especially the bullet about complexity, to introduce execution
from managed code. Complexity is the only disadvantage of executing SSIS from
.NET. Executing SSIS from managed code offers maximum flexibility: If you can
think it, you can find a way to build it in .NET. In my opinion, knowing a .NET
language is no longer optional for the data integration developer in the Microsoft
space.
Execution from Managed Code
There is a ton (or tonne, if you prefer) of benefit from executing SSIS packages from
.NET managed code. There are various limitations to executing SSIS in other ways.
Without exception, they can all be overcome by controlling execution from .NET. In
this section, we will demonstrate the basics of using VB.NET to execute SSIS pack-
ages.