Professional SQL Server™ 2005 Integration Services

1.6. Data Flow Elements

Once you create a Data Flow task, it spawns a new data flow. Just as the Controller Flow handles the main workflow of the package, the data flow handles the transformation of data. Almost anything that manipulates data falls into the data flow category. As data moves through each step of the data flow, the data changes based on what the transform does. For example in Figure 1-7, a new column is derived using the Derived Column transform, and that new column is then available to subsequent transformations or to the destination.

In this section, each of the sources, destinations, and transformations will be briefly covered. These areas are covered in much more detail in Chapters 3 and 4.

Figure 1.7. Figure 1-7

1.6.1. Sources

A source is where you specify the location of your source data to pull from in the data pump. Sources will generally point to the Connection Manager in SSIS. By pointing to the Connection Manager, you can reuse connections throughout your package, because you need only change the connection in one place. There are six sources altogether that can be used out of the box with SSIS:

• OLE DB Source: Connects to nearly any OLE DB data source, such as SQL Server, Access, Oracle, or DB2, to name just a few.

• Excel Source: Source that specializes in receiving data from Excel spreadsheets. This source also makes it easy to run SQL queries against ...