Speaking of Data

Simple trick to Bulk Insert in Slow Changing Dimension Task in SSIS

Data Sensei's photo
Data Sensei
·

2 min read

I am sure my readers have a very good understanding of how SSIS Data Flow Task “Slow Changing Dimension” works. The point of this blog is not to introduce you to slow changing dimension task rather to point out something interesting you can do with it to make it load in bulk. However, I will discuss the all steps of getting to the bulk inserts mode for slow changing dimension.

  1. Start with creating the Dimension tables structures in your data warehouse first. You can pick up the code from here and modify it to meet your needs.
IF OBJECT_ID(N'[dbo].[DimProduct2]') IS NOT NULL --Remove dbo here 
DROP TABLE [dbo].[DimProduct2] GO 
CREATE TABLE [dbo].[DimProduct2](
    [ProductSK] [int] IDENTITY(1,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [ProductName] [nvarchar](50) NOT NULL,
    [Category] [nvarchar](50) NOT NULL,
    [Model] [nvarchar](50) NOT NULL,
    [StandardCost] [money] NOT NULL,
    [ListPrice] [money] NOT NULL,
    [DaysToManufacture] [int] NOT NULL,
    [SellStartDate] [datetime] NOT NULL,
    [EffectiveDate] [date] NOT NULL,
    [ExpiryDate] [date] NULL,
 CONSTRAINT [PK_DimProduct2] PRIMARY KEY CLUSTERED 
(
    [ProductSK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DimProduct2] 
ADD  CONSTRAINT [DF_DimProduct2_EffectiveDate]  DEFAULT (getdate()) FOR [EffectiveDate]
GO

ALTER TABLE [dbo].[DimProduct2] 
ADD  CONSTRAINT [DF_DimProduct2_ExpiryDate]  DEFAULT (NULL) FOR [ExpiryDate]
GO

  1. Come back to creating the slow changing dimension task and configure it. I have disable the inferred member support for this job. What is an inferred member support? Let’s say sometimes, If a fact record references a dimension record that doesn’t fully exist yet, inferred member support creates a placeholder in the dimension table. This allows the fact table record to link to that placeholder, so everything loads without errors. We will talk about it later on. However leave the configuration as you see on the image for now.

    1. Insert Destitation Task will be configured for Bulk Insert AKA fast load. You have to come back in here and change the settings.