h1

An Alternative to the OLE DB Command to update Slowly Changing Dimensions.

March 21, 2012

In a previous post I detailed how to create a package that handles SCD Type 1 changes using SSIS.  The component that handles writing the updates to the destination table inside the data flow is the OLEDB Command. 

image

It is the only native component in the dataflow that can write update statements to the destination table. Unfortunately these update statements are done on a row by row basis.  The question is then, how do we accomplish batch updates in the SSIS package?

The answer is pretty simple:

  • We first have to create a table stage the updates,  with the same structure as our destination table.
      CREATE TABLE [dbo].[StageDimUser](
      [UserSK] [int] NOT NULL,
      [UserAK] [varchar](50) NULL,
      [ScreenName] [varchar](50) NULL,
      [AccountCreateDate] [date] NULL,
      [TimeZone] [varchar](50) NULL,
      [Language] [varchar](50) NULL,
      [GeographySK] [int] NULL,
      [Hash_CD] [int] NULL)
  • Replace the OLE DB Command with another OLE DB Destination Component.

image

  • Connect the new destination to our newly created stage table and insert all the updates to that table
  • Return to the control flow and add an execute SQL task that updates the destination table with the values from the stage table.  The execute SQL task will perform a Batch Update instead of updating row by row.  So even though we’ve added an extra step to the process, the resulting processing time should be much faster.
    UPDATE [Demo].[dbo].[DimUser]
    SET [UserAK] = StageDimUser.[UserAK]
    ,[ScreenName] = StageDimUser.[ScreenName]
    ,[AccountCreateDate] = StageDimUser.[AccountCreateDate]
    ,[TimeZone] = StageDimUser.[TimeZone]
    ,[Language] = StageDimUser.[Language]
    ,[GeographySK] = StageDimUser.[GeographySK]
    ,[Hash_CD] = StageDimUser.[Hash_CD]
    FROM StageDimUser
    WHERE DimUser.UserSK = StageDimUser.UserSK
  • Finally once the updates are done, truncate the stage table to prep the table for the next load.

image

Advertisements

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

%d bloggers like this: