SSIS Notes
Constructing an SQL Server Integration Services (SSIS) package is just as time-consuming as my work on DTS packages. But I must remind myself that, once I was finished polishing these ‘mechanical documents’—procedural documents, they were easy to use and maintain—and relatively self explanatory. Unless Microsoft has some embarrassing version-1.0 incoherence, SSIS should have the same deal. With all the feel-good crap aside, my notes:
- The SSIS approach is designed to eliminate most t-SQL script scenarios that require temporary tables. One tedious alternative is to load values into variables. The Variables pane—like all pane-based tabular entry areas—suck.
- The scope of Package variables cannot be changed in the IDE! Someone recommends BIDS Helper for this (and other) problems (as of today you no longer have to compile the source code to get this feature).
- I prefer ADO.NET connections instead of OLE DB connections (which are the default—probably for a reason). ADO.NET connections do not have “cannot convert between unicode and non-unicode” errors. ADO.NET connections support named parameters of the form
@MyParameter—OLE DB depends on that old question-mark (?) shit. - Certain Data Flow tasks (like the Lookup Task) seem to require OLE DB connections.
- “Yes, you can assign results from Execute SQL Task into variables.” And after that we can “Map Query Parameters to Variables in an Execute SQL Task.” But, clearly, the use of variables is not a replacement for Data Flow source.
- A Script Component Data Flow Transformation can be used as a Data Flow source—and Package variables can be used in scripts. It is important to remember that script components can be used as data sources, destinations or transformations.
- SSIS enforces type safety by telling you to fix type problems (like truncation warnings related to different types)—DTS converts shit behind your back. Ignorance was bliss—but professionalism requires continual improvement.