Professional SQL Server™ 2005 Integration Services

Chapter 6. Advanced Tasks and Transforms

In Chapter 4 you learned about a few tasks and briefly looked over some of the common transformations that you will need in your everyday package development toolkit. If you have some experience with the old SQL Server 2000 DTS, you should be getting a little more comfortable now with the idea that the old dark-arrow data-pump task is gone. The paradigm of hiding transformation-level ActiveX script and embedding connectivity in the transformation task is no more. No longer will you be likely to forget to change the transformation destination when you point packages to other databases on the same server—even though you changed the destination connection. Connections are now created and stored globally. Transformation activity, now managed more easily, has exploded with options that can be viewed in their own design surface in the Data Flow tab.

In a lot of ways you are going to have to restrain yourself in your approach to SSIS package development. It is not necessary to start every new package by opening up an ActiveX script task and using brute force to get things done. In fact, if at first you find yourself in the guts of a script task, just stop. There are so many new tasks and transforms that handle things like removing ragged edges from flat files, splitting incoming content, and looping through tasks in a package that chances are you'll most likely find something that can get the job done. You may still run into things that push past ...