The SSIS Batch Destination component could be used to populate the SCD Type 1 or Type 2 table very easily.
- Create an ADO.NET connection manager to be used.
- Click on the Input Columns tab to select all the input columns needed.
- Go to the Properties tab to write the TSQL merge statements.
- Type in the table name in the Working Table textbox. You may use a temporary table (denoted as #) or a regular table to hold your input data.
- Type in the merge statements in the SQL Command textbox. The Batch Destination component supports multiple SQL Commands in a transaction.
- For SCD Type 1 table, you may use the following TSQL statement to do both update or insert.
MERGE INTO DW.TableName AS T
USING
#TableName AS S
ON T.ID = S.ID
/*Update Record*/
WHEN MATCHED AND S.UpdateFlag = 1
THEN UPDATE
SET
.......
T.Column1=S.Column1,
T.Column2=S.Column2,
T.Column3=S.Column3,
T.Column4=S.Column4
/*Insert record : New record or type 2 current record*/
WHEN NOT MATCHED BY
TARGET THEN INSERT
(
ID,
Column1,
Column2,
Column3,
Column4,
... )
VALUES(
S.ID,
S.Column1,
S.Column2,
S.Column3,
S.Column4,
...);
- For SCD Type 2 table, you may use the following TSQL statements to do update, insert, expire, and update type 1 columns in historical data.
MERGE INTO DW.TableName AS T
USING
#TableName AS S
ON T.ID = S.ID
/*Update Record*/
WHEN MATCHED AND S.UpdateFlag = 1
THEN UPDATE
SET
.......
T.Column1=S.Column1,
T.Column2=S.Column2,
T.Column3=S.Column3,
T.Column4=S.Column4
/*Insert record : New record or type 2 current record*/
WHEN NOT MATCHED BY
TARGET THEN INSERT
(
ID,
Column1,
Column2,
Column3,
Column4,
... )
VALUES(
S.ID,
S.Column1,
S.Column2,
S.Column3,
S.Column4,
...);
/*Expire type 2 existed Record*/
MERGE INTO DW.TableName AS T
USING #TableName AS S
ON T.ID = S.D
WHEN MATCHED AND S.UpdateFlag = 0
THEN UPDATE SET
T.EffectiveEndDate = GetDate(), T.CurrentRecord ='NO'
;
/* Update Type 1 columns in the expired records */
UPDATE T
SET X.Type1Column1 = S.Type1Column1,
X.Type1Column2 = S.Type1Column2
FROM DW.Tablename AS X INNER JOIN DW.TableName AS S
ON X.NK= S.NK /*Join table with Natural keys */
WHERE S.CurrentRecord = 'YES'
AND S.ID > X.ID
;
There are some performance issues you may encountered while working with large table, so you will need to change the
Rows Per Batch or
Max Commit Size properties accordingly. Sometime you may find that creating an index in your temporary table could improve the performance of this load.
