Managing Dimension Tables Part 2

In the previous section, you learned to use the Left Outer Join task to detect data changes that could affect your dimension table load processing. The Left Outer Join Merge task is a very powerful way to perform this kind of data inspection. The Left Outer Join Merge task reads all the data to perform the detection of changes. You will now learn an alternative way to perform this dimension data inspection.

Loading Dimension Tables Part 2

In this next procedure, you will learn how to achieve the same results as shown in the previous procedure but using a different technique for finding new dimension members. In the following procedure, you will learn how to use the Lookup task.

Add a New Package

1. Right-click SSIS Packages and choose New SSIS Package. Right-click the new package and rename it  DW_DimMgmt_2.dtsx.

2. Drag a Data Flow task from the Toolbox window onto the Control Flow workspace. Right-click the task and rename it Find New Dim Members Process 2.

3. Double-click the Data Flow task to activate the Data Flow tab. From the Data Flow Sources group in the Toolbox, drag an OLE DB Source object onto the designer.

4. Right-click the new OLE DB source and rename it Stage Customer Table.

5. Double-click Stage Customer Table and select New to create a new OLE DB connection. Select localhost.SSIS DW and click OK.

6. Select [dbo].[DimStageCustomer] from the drop-down list of Name Of The Table Or The View. Click OK.

   Your screen should look like this:

   

Add a Lookup Task Process

1. Drag a Lookup task from the Data Flow Transformations group in the Toolbox onto the Data Flow workspace and connect it to the Stage Customer Table data source with the green arrow.

2. Double-click the new Lookup task and select localhost.SSIS DW from the OLE DB Connection Manager.

3. Select the [dbo].[DimCustomer] table as Reference table from the Use A Table Or A View drop-down list.

4. Click the Columns tab and select CustomerKey in Available Lookup Columns. Set Lookup Operation to <add as new column>. Set Output Alias to LookupValue.

   Your screen should look like this:

   

5. Click Configure Error Output and set Lookup Output to Redirect Row. Set the LookupValue Truncation column to Redirect Row.

   Your screen should look like this:

   

   Note 

   All rows that do not have corresponding rows in the existing dimension table will be redirected to error output.

6. Click OK twice to close the editors.

Add a Flat File Destination

1. From the Data Flow Destinations group in the Toolbox, drag a Flat File Destination onto the designer and connect it to the red error output arrow from the Lookup task. Click OK in the Configure Error Output window.

2. Double-click the new Flat File destination and click the New button to configure it.

3. Click OK to accept Delimited as the flat file format.

4. In the Connection Manager name field, type New Found Customers 2.

5. Click Browse and navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Data\NewDimCustomers2.txt.

6. Select the Column Names In The First Data Row check box, and then click OK.

7. Select the Overwrite Data In The File check box and click Mappings in the left pane.

8. Click OK.

9. Click Save All on the toolbar to save the package.

10. Right-click  DW_DimMgmt_2.dtsx in Solution Explorer and choose Execute Package.

    Your screen should look like this:

    

    Note 

    Six new customer rows should be written to the destination file.

11. Click Stop Debugging on the Debug menu.

12. Right-click Flat File Destination and choose Edit. Choose Preview to view the data.

13. Click Close and then OK to exit the editor.

    Note 

    Both of these SSIS packages achieve the same result and, usually, you would append the new members directly to the dimension table instead of creating a flat file destination. The second technique (using the Lookup task) is much easier. It is important to remember that using the lookup error output in this manner can be confusing for some users.