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 (
- 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.