Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

50



LESSON 6 Using the Data Flow

If additional transformations are needed on the data set before it is loaded into the destination,

simply drag over any component and connect it to the derived column. For example, if you wanted

to load only rows that meet a certain condition, you could use a Conditional Split task. Or if you

wanted to load the data into multiple destinations you could use a Multicast task.

DATA FLOW PATHS

You can connect the items that have been added to the Data Flow designer by using the data flow

paths that protrude from each element. At a minimum each item will have a success path (green

arrow) and an error path (red arrow). To use these paths, simply drag one from an item onto the next

item you want to execute. At that point, the items will execute in the sequence that you have specified.

In some cases, you can customize the output of a data flow path. For example, with the Lookup task

you can specify what if anything is output from the error flow path. In Figure 6-7 you can see the

four configurable options.

If you choose the first option,



“Ignore failure,” from the drop-down list labeled “Specify

how to handle rows with no matching entries,” the Lookup task will behave much like an

OUTER JOIN in T-SQL.

Choosing



“Redirect rows to error output” will send any rows that do not match down the

error flow path. Two additional columns are added to the output columns. ...