Saturday, January 20, 2018

SCD TYPE-2 using tsql SQL Server


Below workaround demonstrates how to maintain a SCD-TYPE-2 in a slowly changing dimension using T-SQL SQL Server.

Definition of SCD Type-2 :
When  records come from source table and same records present in target table, then at that situation how to handle the existing records in target table.
In type-2 we make versions of each records in target table meaning keep the previous record inactive and keep the most current records active.


Step-1 Source table looks like below

DECLARE @Source AS TABLE (ID INT , Col1 VARCHAR(10),Col2 VARCHAR(10),Col3 VARCHAR(10))
INSERT @Source
SELECT 1,'a','a','a1'
UNION ALL
SELECT 2,'a','a','a'
UNION ALL
SELECT 3,'a','a','b'

SELECT  * FROM @Source
Output:




Step-2:
Target table looks like below

DECLARE @Target AS TABLE (ID INT , Col1 VARCHAR(10),Col2 VARCHAR(10),Col3 VARCHAR(10),ValidFrom DATETIME,ValidTo DATETIME,IsActive BIT)
INSERT @Target
SELECT 1,'a','a','a',GETDATE()-1,NULL,1

SELECT * FROM @Target
Output:


DECLARE @Temp AS TABLE (ID INT , Col1 VARCHAR(10),Col2 VARCHAR(10),Col3 VARCHAR(10),ValidFrom DATETIME,ValidTo DATETIME,IsActive BIT)

DECLARE @NewID AS TABLE (ID INT)


Step-3:
Insert new records in target along with old records in temp table
Make ValidFrom of new records to Current Run Date 
ValidTo = NULL
IsActive Flag = 1


--New Records
INSERT @Target(ID,Col1,Col2,Col3,ValidFrom,ValidTo,IsActive)
OUTPUT inserted.ID INTO @NewID
SELECT S.*,GETDATE(),NULL,1 FROM
@Source S
LEFT JOIN @Target T ON S.ID = T.ID
WHERE T.ID IS NULL


Step-4:
Update below columns in existing records in target table
  ValidFrom = GETDATE(),
  ValidTo = NULL,
  IsActive = 1

Insert deleted records in Temp table for further insert

UPDATE T
SET T.ID         = S.ID,
    T.Col1 = S.Col1,
    T.Col2 = S.Col2,
    T.Col3 = S.Col3,
    T.ValidFrom = GETDATE(),
    T.ValidTo = NULL,
    T.IsActive = 1
OUTPUT DELETED.* INTO @Temp
FROM @Source S
INNER JOIN @Target T ON  S.ID = T.ID
WHERE HASHBYTES('SHA1',CONCAT(S.Col1,'|',S.Col2,'|',S.Col3)) != HASHBYTES('SHA1',CONCAT(T.Col1,'|',T.Col2,'|',T.Col3))
AND T.ID NOT IN(SELECT ID FROM @NewID)


Step-5:
Insert records from temp table with Inactive flag = 0 and ValidTo column is Rundate.

INSERT @Target(ID,Col1,Col2,COl3,ValidFrom,ValidTo,IsActive)
SELECT ID,Col1,Col2,COl3,ValidFrom,GETDATE(),0 FROM @Temp

SELECT * FROM @Target
 Output:




SSIS script component with Multiple delimiter flat file


How to handle a flat file which contains multiple delimiters and same delimiters does not support in Text qualifier. We can handle this scenario using this Link along with script component describes below.

Howto handle multiple delimiters using SSIS script component .

Lets say below file is the sample flat file and delimiter is ","






Steps: Take a script component as source .


  1. Double click and go to Inputs and Outputs option -> Add required columns in my case I added 30 columns
  2. Go to connection managers -> Give the connection name. In this example it's "Connection". Which will be used in subsequent script.







C# Script to handle multiple delimiters.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
#endregion

 [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    private StreamReader textReader;
    private string exportedFile;

    public override void AcquireConnections(object Transaction)
    {

        IDTSConnectionManager100 connMgr = this.Connections.Connection;//Connection name from step-2
        exportedFile = (string)connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {
        base.PreExecute();
        textReader = new StreamReader(exportedFile);
    }

    public override void PostExecute()
    {
        base.PostExecute();
        textReader.Close();
    }

    public override void CreateNewOutputRows()
    {
        string nextLine;
        string[] columns;

        
        //Logic for multiple delimiters ","
        string[] delimiters = new string[]  {"\",\""};
        nextLine = textReader.ReadLine();
        while (nextLine != null)
        {
            columns = nextLine.Split(delimiters, StringSplitOptions.None);
            {
                OutputBuffer.AddRow();
                OutputBuffer.Column = columns[0];
                OutputBuffer.Column1 = columns[1];
                OutputBuffer.Column2 = columns[2];
                OutputBuffer.Column3 = columns[3];
                OutputBuffer.Column4 = columns[4];
                OutputBuffer.Column5 = columns[5];
                OutputBuffer.Column6 = columns[6];
                OutputBuffer.Column7 = columns[7];
                OutputBuffer.Column8 = columns[8];
                OutputBuffer.Column9 = columns[9];
                OutputBuffer.Column10 = columns[10];
                OutputBuffer.Column11 = columns[11];
                OutputBuffer.Column12 = columns[12];
                OutputBuffer.Column13 = columns[13];
                OutputBuffer.Column14 = columns[14];
                OutputBuffer.Column15 = columns[15];
                OutputBuffer.Column16 = columns[16];
                OutputBuffer.Column17 = columns[17];
                OutputBuffer.Column18 = columns[18];
                OutputBuffer.Column19 = columns[19];
                OutputBuffer.Column20 = columns[20];
                OutputBuffer.Column21 = columns[21];
                OutputBuffer.Column22 = columns[22];
                OutputBuffer.Column23 = columns[23];
                OutputBuffer.Column24 = columns[24];
                OutputBuffer.Column25 = columns[25];
                OutputBuffer.Column26 = columns[26];
                OutputBuffer.Column27 = columns[27];
                OutputBuffer.Column28 = columns[28];
                OutputBuffer.Column29 = columns[29];
                OutputBuffer.Column30 = columns[30];
            }
            nextLine = textReader.ReadLine();
        }

    }


}