Implementation of SCD in SSIS using T-SQL

For this example I use a text file with client information that I want to use to populate my Client dimension. To keep it simple I just use 4 columns: BusinessKey, Title, Firstname and Lastname.


1) Variables
For logging purposes we need to add three integer variables: staged, inserts, updates:

Variables for logging

2) Staging
Next step is to load the flat file with clients into a staging table with a Data Flow Task. The staging table is truncated before loading and the number of records in the Data Flow Task is stored in the variable staged with a rowcount transformation. It should look something like this below:

3) Execute SQL Task
Now we have two tables (staging and dimension) and we need to merge them: 

Staging and dimension

For this we need to add an Execute SQL Task.

Execute SQL Task

4) Execute SQL Task - General
Edit the task and set the ResultSet to Single row, select the OLE DB connection and enter the MERGE query. The MERGE query below does INSERTS and UPDATES only. We don't delete records from the dimension table. For logging purposes I added a count of the number of inserts and updates.

General pane

The query to use:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

-- Create a table variable to store the number of updates and inserts DECLARE @tableVar TABLE (MergeAction VARCHAR(20))   -- The actual MERGE statement MERGE  dwh.Dim_Client as MyTarget USING  (    SELECT BusinessKey    ,  Title    ,  FirstName    ,  LastName    FROM staging.Clients    ) as MySource    ON MyTarget.BusinessKey = MySource.BusinessKey    WHEN MATCHED AND NOT (     MySource.Title   = ISNULL(MyTarget.Title, '')     AND MySource.FirstName = ISNULL(MyTarget.FirstName, '')     AND MySource.LastName = ISNULL(MyTarget.LastName, '')    )    THEN -- BusinessKey of client exists, but data is different     UPDATE      Set MyTarget.Title  = MySource.Title     , MyTarget.FirstName = MySource.FirstName     , MyTarget.LastName = MySource.LastName    WHEN NOT MATCHED BY TARGET    THEN -- BusinessKey of client does not exist     INSERT (BusinessKey, Title, FirstName, LastName)     VALUES (MySource.BusinessKey, MySource.Title, MySource.FirstName, MySource.LastName) OUTPUT $action INTO @tableVar;   -- Get the number of inserts and updates on one line SELECT  SUM(Inserted) as Inserted ,   SUM(Updated) as Updated FROM  (    -- Count the number of inserts    SELECT COUNT(*) as Inserted, 0 as Updated    FROM @tableVar     WHERE MergeAction = 'INSERT'      UNION ALL    -- Count the number of updates    SELECT 0 as Inserted, COUNT(*) as Updated    FROM @tableVar     WHERE MergeAction = 'UPDATE'    ) as CountTable;

5) Execute SQL Task - Result Set
Go to the Result Set pane and select the insert and update variables for storing the counts.

Result Set pane

6) The Result
For testing purposes I added a ridiculous number of clients in my text file and instead of logging to a table I added a MessageBox in a Script Task.

The result

Source: http://microsoft-ssis.blogspot.in/