Professional SQL Server™ 2005 Integration Services

4.4. Transformations

Transformations(the term transform will be used throughout this book) are key components to the data flow that transform the data to a desired format as you move from step to step. For example, you may wish a sampling of your data to be sorted and aggregated. Three transforms can accomplish this task for you. The nicest thing about transforms in SSIS is that it is all done in-memory and it no longer requires elaborate scripting as in SQL Server 2000 DTS. As you add a transform, the data is altered and passed down the path in the data flow. Also, since this is done in-memory, you no longer have to create staging tables to perform most functions. When dealing with very large data sets, though, you may still choose to create staging tables.

You set up the transform by dragging it onto the data flow tab design area. Then, click the source or transform you'd like to connect it to, and drag the green arrow to the target transform or destination. If you drag the red arrow, then rows that fail to transform will be directed to that target. After you have the transform connected, you can double-click it to configure the transform.

In this chapter you will look at each of the transforms at a basic level. In the next chapter, you'll take some of the common tasks and transformation on a test drive and use them in a few real-world scenarios. There are a few transformations that are more advanced, which will be described briefly here but will be covered in much more detail ...