SSIS Package Exercise Steps: Upsert 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. From the Control Flow, find the Load Rows sequence.

4. Double-click the Upsert task. The Execute SQL Task Editor screen appears.

5. From General, under SQL Statement,in the SQLStatement field, highlight the field and click the edit (ellipses) button. The Enter SQL Query screen appears.

6. Modify the MERGE to reflect the names of your warehouse table and your staging table. In this exercise, the column names are the same because the package is based on duplicate tables. Fortunately, the tables are aliased. So there are only two places to update: after MERGE and FROM. The query should look like this:

   declare @COUNTS table([ACTION] varchar(28), [INSERTED] int, [UPDATED] int);
   merge BBDW.[FACT_INTERACTIONRESPONSE_EXT] as t
   using 
   (
     select
         ir.[INTERACTIONRESPONSESYSTEMID],
         i.[INTERACTIONFACTID],
         ir.[RESPONSEDIMID],
         ir.[INTERACTIONRESPONSEDATE],
         ir.[INTERACTIONRESPONSEDATEDIMID],
         ir.[ISINCLUDED],
         ir.[ETLCONTROLID],
         ir.[SOURCEDIMID]
     from BBDW.[FACT_INTERACTIONRESPONSE_EXT_STAGE] ir
     inner join BBDW.[FACT_INTERACTION] i on i.[INTERACTIONSYSTEMID] =  ir.[INTERACTIONSYSTEMID]
   ) as s
   on (t.[INTERACTIONRESPONSESYSTEMID] = s.[INTERACTIONRESPONSESYSTEMID])
   when not matched by target
     then insert(
      [INTERACTIONRESPONSESYSTEMID],
      [INTERACTIONFACTID],
      [RESPONSEDIMID],
      [INTERACTIONRESPONSEDATEDIMID],
      [INTERACTIONRESPONSEDATE],
      [ISINCLUDED],
      [ETLCONTROLID],
      [SOURCEDIMID]
     ) values (
       s.[INTERACTIONRESPONSESYSTEMID],
       s.[INTERACTIONFACTID],
       s.[RESPONSEDIMID],
       s.[INTERACTIONRESPONSEDATEDIMID],
       s.[INTERACTIONRESPONSEDATE],
       s.[ISINCLUDED],
       s.[ETLCONTROLID],
       s.[SOURCEDIMID]
     )
   when matched
     then update
       set 
       t.[INTERACTIONRESPONSESYSTEMID] = s.[INTERACTIONRESPONSESYSTEMID],
       t.[INTERACTIONFACTID] = s.[INTERACTIONFACTID],
       t.[RESPONSEDIMID] = s.[RESPONSEDIMID],
       t.[INTERACTIONRESPONSEDATEDIMID] = s.[INTERACTIONRESPONSEDATEDIMID],
       t.[INTERACTIONRESPONSEDATE] = s.[INTERACTIONRESPONSEDATE],
       t.[ISINCLUDED] = s.[ISINCLUDED],
       t.[ETLCONTROLID] = s.[ETLCONTROLID],
       t.[SOURCEDIMID] = s.[SOURCEDIMID]
     output $action, 
     case when deleted.[ETLCONTROLID] is null then 1 else 0 end,
     case when deleted.[ETLCONTROLID] is not null then 1 else 0 end into @COUNTS;
   select  count(*) as [TOTAL], isnull(sum([INSERTED]),0) as [INSERTED], isnull(sum([UPDATED]),0) as [UPDATED]
   from @COUNTS

7. Click OK. Close the editor.

8. Save the package.