Chapter 6: Advanced Tasks and Transforms

Overview

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 transformationlevel 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 the limit of canned SSIS capabilities. If this happens, open up a .NET script task and access the full power of the .NET framework.

This chapter is about the nuts and bolts of getting thing done. You are going to put these new tools to work on some real-world problems. Do you need to retrieve settings from stored procedure output parameters? Conditionally branch into logic based on a row count? Generate some meaningful statistics from free-form text data? How about using fuzzy logic to standardize data for lookup tables while you load it? You'll look at these and other advanced implementations of some of the tasks, and then later you'll explore some of the more complex transformations. It is assumed that you are familiar enough with the BIDS environment to build packages, so this chapter will not explain how to do this in detail.