SSIS: DataFlow

The primary component for extracting, transforming, and loading data.

First, drag an OLE DB source unto the designer.

1-dataflow

Modify the data source by creating a new connection manager and the correct database and table.
For this example, I will only leave three columns selected for retrieval.

Now drag/drop a Derived Column transformation and connect the green line between the data source and transformation.

4-dataflow

Configure the transformation using the String UPPER() function to aggregate the First and Last name values into a new derived column.

Drag a DataReader destination unto the designer and connect the Derived Column’s green line to it.

6-dataflow

Right-click the destination to configure its input using the Advanced Editor.

Add a Dataviewer between the data source and the derived column by right-clicking the pipeline between them, select Dataviewer.
Add a Grid viewer and configure the columns for display.

Now use the same steps to add a Dataviewer between the Derived Column and the destination using the same steps.

Execute the package and notice the it stops with both the source and destination are yellow indicating execution has paused.
This is due to the first data viewer has been populated and awaiting response from the user.

Choosing to “Detach” the first viewer allows the second viewer to be populated and the source’s color is now green.
By detaching again, the package will complete execution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s