SSIS Data Flow

We can add tasks to the Data Flow from the Toolbox on the left hand side.

To open the Data Flow in the designer, go to the Data Flow tab or simply double click the Data Flow task from the Control Flow.

Let us first add a Data Flow Source. Pick the OLE DB Source and drag it to the design window.

Double click the added OLE DB Source object to define its source connection manager and its source table. The source is the DD_W1_01 database, and the first table to transfer is the Country_Region table.

Click OK when completed and rename the object to Country_Region Source.

Now we must add a Data Flow Destination. From the Toolbox, select OLE DB Destination and drag it to the design window.

Rename the destination object to Country_Region Destination and drag the blue pointer from the Source object to the Destination object.

Now double click the destination object to start defining it. Make sure that you pick the Stage database as your destination.

Go to the Mappings page to map source columns to destination columns.

Click OK when completed. At this point you might still see an error:

The error is caused by the timestamp column in the Country_Region table, because it is a read-only column. In fact you will see this error every time you are trying to insert into a read-only type column. We can safely ignore the timestamp column in our solution as it does not provide any valuable information to us.

To ignore, we can simply remove it from the mappings of the OLE DB destination definition.

Repeat these steps for the other tables Customer, Item, Sales Invoice Line and Sales Invoice Header. The final result may look like this:

Now that we have a package that transfer data from our source tables to our destination table, we can try and run it manually – just to make sure that it is able to complete successfully.

To run the package manually we must “Start Debugging”. This is done by pressing the F5 button or by clicking the button in the toolbar.

While the package is running we can see progress on either the Control Flow tab or on the Data Flow tab. Also we will immediately see if something fails or, when completed, if everything succeeded.

After a failed or successful run you will have to manually stop the debugging as well. Do this with Shift + F5 or by clicking the “Stop Debugging” button in the toolbar.