SSIS Package Exercise Steps: Load Changed Rows Task

  1. From Visual Studio 2008, open your Business Intelligence project.

  2. From Solution Explorer, double-click the SSIS package to modify. The SSIS package appears.

    To stay on track with the rest of the exercises, open the package created in the previous exercise, BBDW_FACT_INTERACTIONRESPONSE_EXT.dtsx.

  3. Switch from the Control Flow view to the Data Flow view.

  4. Double-click the first Data Flow Component. For example, this may appear as New and changed rows from OLTP. The OLE DB Source Editor appears.

    The columns in the Transact-SQL command text should match your desired tables because the package is based on one for a duplicate table.

  5. The SQL command text should look like this:

    select
  IR.[ID] as [INTERACTIONRESPONSESYSTEMID],
  IR.[INTERACTIONID] as [INTERACTIONSYSTEMID],
  IR.[RESPONSEID] as [RESPONSESYSTEMID],
  IR.date as [INTERACTIONRESPONSEDATE],
  1 as [ISINCLUDED]
from dbo.[INTERACTIONRESPONSE] as IR 
where  (IR.[DATECHANGED] > ? and IR.[DATECHANGED] <= ? )

    Since you haven't changed anything in the OLTP database and your extension table for the BBDW database mirrors a table designed to pull from the OLTP table, INTERACTIONRESPONSE, there is nothing to change here.

    In this task you would normally create a SELECT query that grabs changed rows from the OLTP database. The SELECT query is parameterized for changed dates.

  6. Now double-click the Changed row count task. The Advanced Editor for Changed Row Count screen appears.

    There is nothing to change here. The task sets the value of the variable RowCountAll to the number of rows in the dataset. Close the editor.

  7. Double-click Add ETLControlID. The Derived Column Transformation Editor screen appears.

    There is nothing to change here. The task adds derived columns for ETL control. Close the editor.

  8. Double-click Date Dims. The Derived Column Transformation Editor screen appears.

    There is nothing to change here for this exercise. But depending on the extent of your modifications, there could be. Notice the name of the derived column does not sound generic. INTERACTIONRESPONSEDATEDIMID is specific to the intent of the package. The derived column in this case supports date dimensions. Because there are so many possible values for dates and times, it faster and considered a best practice to generate the key rather than look it up. Close the editor.

  9. Double-click Check Dates. The Derived Column Transformation Editor screen appears.

    Again, there is nothing to change here for this exercise. But depending on the extent of your modifications, there could be. Close the editor.

  10. Double-click Lookup Response. The Lookup Transformation Editor appears.

    The name of this task should throw a flag. It is not generic. Lookup transformations are common in packages. But this one has the word Response in its name. Now look at the Connection page of the editor. This Transact-SQL code is displayed:

    SELECT        RESPONSEDIMID, RESPONSESYSTEMID
FROM            BBDW.DIM_RESPONSE

    That code represents the lookup. You may need to create a task similar to this or modify this one at some point. For the purposes of this exercise, you do not need to modify anything. Close the editor.

  11. Double-click Stage rows. The OLE DB Destination Editor screen appears.

  12. Adjust the name of the table from FACT_INTERACTIONRESPONSE_STAGE to FACT_INTERACTIONRESPONSE_EXT_STAGE. Close the editor.

  13. Save the package.