Incremental Load Made Easy in Case of UPDATE Records

Introduction

Here i am taking about the Incremental Load of Data in SSIS. All developer use there own methodology to complete the process. This artilce is related to the UPDATE the existing records of Destination in case of Incremental Load.

Hope it will be informative and put some values in your professional work.

What we Actually Do in Incremental Load

Here we are just mentioned the Alogitham behind the Incremental Load.

1. We check if the Records Exists in the Destination or Not by comparing with Primary key and if the records doesn't Exists  it just insert the records in destination table.
   
2. If the Records EXIST then it checks the other columns. If the other columns changes then it goes for UPDATION of records.

Why the UPDATION Process is so Cumbersome

Think we have a table and the table has more than 60 columns and one Primary key. If we compare both Source and Destination Table columns each other’s like this

[Source].[Column-A] == [Destination].[Column-A]

OR [Source].[Column-B] == [Destination].[Column-B]

OR [Source].[Column-C] == [Destination].[Column-C]

OR [Source].[Column-D] == [Destination].[Column-D]

…. 60 columns like this

Think what happens if we are going to maintain such kind of code.

So what the Solutions

To solve this we have to introduce Columns both is Source and Destination Table. This column contains some vale based on Checksum/Binary Checksum / Hash bytes values. We just compare those values and decide whether UPDATION is needed or NOT.

So it is quite easy to compare then compare columns by columns.

Take an Example of CHECKSUM

SELECT EmpId,

EmpName,

EmpGrade,

CHECKSUM(EmpName, EmpGrade) AS [CheckSum_Val]

FROM tbl_Employee;

EmpId    EmpName            EmpGrade       CheckSum_Val

1             Joydeep Das         A                      -1141869962

2            Deepasree Das     B                        1652695494

3            Sukamal Jana        A                        2067120924

4            Santi Ranjan          C                       -1747286364

Take an Example of BINARY_CHECKSUM

SELECT EmpId,

EmpName,

EmpGrade,

BINARY_CHECKSUM(EmpName, EmpGrade) AS [BinaryCheckSum_Val]

FROM tbl_Employee;

EmpId    EmpName        EmpGrade    BinaryCheckSum_Val

1            Joydeep Das      A                    841019011

2            Deepasree Das  B                    1196883990

3           Sukamal Jana     A                     2115519786

4           Santi Ranjan       C                    1412284040

Take an Example of HashBytes

SELECT EmpId,

EmpName,

EmpGrade,

CHECKSUM(HASHBYTES('MD5',EmpName), HASHBYTES('MD5',EmpGrade)) AS [HashBytes_Val]

FROM tbl_Employee;

EmpId                 EmpName          EmpGrade          HashBytes_Val

1                         Joydeep Das       A                        -641922184

2                         Deepasree Das   B                       -1681185931

3                         Sukamal Jana     A                         750240776

4                        Santi Ranjan        C                         1481016726

So which one We Prefer

We have to choose between CHECKSUM, BINARY_CHECKSUM and HASHBYTES

When we go to the CHECKSUM method the MS Documentation gives us some sort of Shocking news.

“However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change”

https://msdn.microsoft.com/en-us/library/ms189788(v=SQL.100).aspx?tduid=(ebe921475c6d9b507dcd22ad377d2077)(256380)(2459594)(TnL5HPStwNw-6X_zL_AiThyx782dcyEmKA)()

BINARY_CHECKSUM is also not good reputation.

https://msdn.microsoft.com/en-us/library/ms173784(v=SQL.100).aspx?tduid=(ebe921475c6d9b507dcd22ad377d2077)(256380)(2459594)(TnL5HPStwNw-adFXijJAN_quNB1GnSuyQQ)()

So HASHBYTES is the safest one that we can use.

Hope you like it.

Posted By: MR. JOYDEEP DAS

SSIS – Incremental Process by CHECKSUM() Function

Introduction

In the journey of my SSIS here we are going to demonstrate one of the processes of Incremental data Load by using CHECKSUM() Function. Hope it will be interesting.

What the Scenario is

Scenario is simple load data from a staging table to Destination table. The Staging table is populated from Flat File source. Here in this article we are not interested to Load the Staging Table but interested to understand how we use the CHECKSUM() function in SQL Server for Incremental data load.

How we do That

 Step – 1 [ The Data Flow of the Package ]

Step – 2 [ The Staging and Destination Table ]

CREATE TABLE [dbo].[tbl_EmployeeStage]

  (

      EmpId   INT            NOT NULL PRIMARY KEY,

      EmpName  VARCHAR(50)    NOT NULL,

      EmpGrade CHAR(1)

  )

GO

CREATE TABLE [dbo].[tbl_Employee]

  (

      EmpId   INT            NOT NULL PRIMARY KEY,

      EmpName  VARCHAR(50)    NOT NULL,

      EmpGrade CHAR(1)

  )

GO

INSERT INTO [dbo].[tbl_EmployeeStage]

    (EmpId, EmpName, EmpGrade)

VALUES(1, 'Joydeep Das', 'A'),

      (2, 'Rajesh Mondal', 'C'),

      (3, 'Santi Nath', 'B');

Step – 2  [ OLEDB – Source for Retrieving data from Source and Destination Table ]

The SQL Command Text for Staging Table

SELECT EmpId, EmpName, EmpGrade, CHECKSUM(*) AS [CheckSum]

FROM   [dbo].[tbl_EmployeeStage]

ORDER BY 1;

The SQL Command Text for Destination Table

SELECT EmpId, EmpName, EmpGrade, CHECKSUM(*) AS [CheckSum]

FROM   [dbo].[tbl_Employee]

ORDER BY 1;

Step – 3  [ The Sort Transform and The Merge Join Transform ]

 Not going to Describe.

Step – 4 [ The Conditional Split ]

Step – 5 [ The Conditional Split named Record Change ]

Please look at the Condition. Here we use the Columns that is made by CHECKSUM() Function. We are not going no check like
[Source Col-1] <> [Destination Col-1] OR [Source Col-2] <> [Destination Col-2] Approach.

Think if you have 150 columns in your table…. What the Situation you face over here.

Step – 6 [ First Time Execution of Package ]

Step – 7 [ Execute Package When Some Records UPDATED in Staging Table ]

UPDATE [dbo].[tbl_EmployeeStage]
       SET    EmpName = 'Suman Das'
WHERE  EmpId = 1

Step – 8  [ Execute Package When Records UPDATED and INSERT in Staging Table ]

UPDATE [dbo].[tbl_Employee] SET EmpName = 'Sree Devi' WHERE EmpId = 2;

GO

INSERT INTO [dbo].[tbl_EmployeeStage]

    (EmpId, EmpName, EmpGrade)

VALUES(4, 'Sunny', 'C');

GO

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS – Dynamic Excel Sheet Choosing in Destination

Introduction
The Article is the continuation of one of my previous article named 

“SSIS – Where Destination is Excel”

Web Ref:

 http://sqlknowledgebank.blogspot.in/2015/12/ssis-where-destination-is-excel.html

To continue this article you must read the above article as the Scenario is the same only we have to make it dynamically.

If you go to the previous article you can find that we are taking three Excel Destination for storing data in there different Sheet of same Excel Work book. After publishing this article one of my friends say in comments that “Can We Make Dynamically”. Here is the solution for that.

Here it is important to remember that we are not creating any Excel Sheet but we used the pre formatted excel sheet in the work book.

Hope it will be interesting.

 

What the Scenario is

The Scenario is same as the previous article scenario named “SSIS – Where Destination is Excel”. You can find it in
http://sqlknowledgebank.blogspot.in/2015/12/ssis-where-destination-is-excel.html

So I am not going to re-type the scenario again.

 

How we Solve it

Step – 1 [ The Base Table and Insert Some Records in it ]

CREATE TABLE [dbo].[tbl_Employee]

  (

      EmpId             INT         NOT NULL IDENTITY PRIMARY KEY,

      EmpName           VARCHAR(50) NOT NULL,

      EmpGrade          CHAR(1)     NOT NULL,

      EmpDepartment     VARCHAR(50) NOT NULL

  );

GO

INSERT INTO [dbo].[tbl_Employee]

   (EmpName, EmpGrade, EmpDepartment)

VALUES  ('Joydeep Das', 'A', 'DBA'),

        ('Sukamal Jana', 'B', 'DBA'),

        ('Subrata Kar', 'C', 'DBA'),

        ('Avijit Gurui', 'A', 'Project Manager'),

        ('Subdip Das', 'B', 'Project Manager'),

        ('Arabinda Sarkar', 'A', 'Development'),

        ('Santi Nath', 'C', 'Development'),

        ('Indrajit Sarkar', 'B', 'Development');

GO

Step – 2 [ The Control Flow Tab ]

Step – 3 [ The  Variable List  ]

Variable Name	Data Type
v_DepartmentName	String
v_ExcelSheetName	String
v_ObjDepartment	Object

Step – 4 [ The  Execute SQL Task ]

Here we find the distinct Department name by which we ebstruct data from table and find the Excel Sheet name.

SQL Statement

SELECT DISTINCT EmpDepartment FROM [dbo].[tbl_Employee];

Step – 5 [ The  ForEach Loop Container ]

Step – 6 [ The  Expression Task  ]

Here we are trying to populate the Excel Sheet name

Expression

@[User::v_ExcelSheetName]= @[User::v_DepartmentName]+"$"

Step – 7 [ The  Data Flow Task  ]

OLEDB Source

SQL Command

SELECT EmpId, EmpName, EmpGrade

FROM   [dbo].[tbl_Employee]

WHERE  EmpDepartment =?

Step – 8 [ The  Data Conversion  ]

Here we Just convert the data type of EmpName and EmpGrade from DT_STR to DT_WSTR

Step – 8 [ The  Excel Destination  ]

It is very important. Where we are dynamically change the Excel Sheet name.

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS – Error Handling Design Pattern in Data Flow

Introduction

I have a request to handle the Error in Destination of data flow from my friends circle. No data source is perfect. So when we design the SSIS package we must take care of Error handling portion also.

The error portion of the OLEDB Destination ma came for Primary Key Violation, CHECK Constraint Violation etc. But if we design the package like it takes data validation before inserting into destination. Sounds good but it is an over head for the package.

Some approach is removing Primary key and all the Constraint before inserting records and after inserting re-create them. It also sounds good but if the garbage data insert in our destination table we are unable to create the constraint and another task we need to perform is removing the garbage from destination.

So approach is many but we have to carefully choose them before implementation which one is suited our situation.

In this article we are not going to describe all components or task in the package and we hope that the reader’s knows the Data Flow error handling process of SSIS.

Hope this article will be informative.

The Scenario

The scenario is simple. Retrieve the data from flat file and insert it into destination table. We have a flat file named Employee Details. It contains the employee data but data is not perfect over here. Some garbage is there.

We have a destination table and it contains a Primary key and Check constraint over Employee Grade. So if any kind of Primary Key, Data Type, Check Constraint Violation is not allowed over here.

Hope you understand the Scenario.

The Design Approach

If we closer look at the Design Approach we find that all the Redirection of Error output is in a UNION ALL transform. Now we can store the output of Error from Union All transform to anywhere like Fat File or Database Table.  

The Destination Table Objects Definition

IF OBJECT_ID(N'[dbo].[tbl_EmployeeDetails]', N'U') IS NOT NULL

   BEGIN

       DROP TABLE [dbo].[tbl_EmployeeDetails]

   END

GO

CREATE TABLE [dbo].[tbl_EmployeeDetails]

   (

       EmpId     INT          NOT NULL PRIMARY KEY,

       EmpName   VARCHAR(50)  NOT NULL,

       EmpDesig  CHAR(1)      NOT NULL,

       EmpDepart VARCHAR(50)  NOT NULL

   );

GO

-- Check Constraint --

ALTER TABLE [dbo].[tbl_EmployeeDetails]

ADD CONSTRAINT chk_EmpDesig CHECK (EmpDesig IN ('A', 'B', 'C'));

GO

The Flat File Sample

If we closer look we can find that the Department “D” Makes the CHECK Constraint Violation in the Destination and Employee Id “Four” make error in Data Conversion Task.

Run the Package and Observe

Hope you like it.

Posted by: MR. JOYDEEP DAS