Using SSIS Batch Destination component to load the SCD Dimension Table

The SSIS Batch Destination component could be used to populate the SCD Type 1 or Type 2 table very easily.

• Download the Batch Destination Component from CodePlex  SSIS Community Tasks and Components Web Site.
• Use it as the Destination Component in the Data Flow.

• 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.