Wednesday, 11 November 2015

Incremental Load without CDC in SSIS

Introduction
In my previous article we are demonstrating CDC as Incremental load in SSIS. But what happens, if CDC is not there. We can do it without CDC also. Here in this article we are going to demonstrate same Incremental load without CDC. Hope it will be informative and you enjoy the session.

What the scenario is
We are taking the same Scenario that we take in the CDC.
We have a table object named tbl_EmployeeDetails like this


EmpID (PK)
EmpName
Designation
DOJ
1
Joydeep Das
A
2015-02-01
2
Deepasree Das
B
2013-05-01
3
Santi Nath Mandal
A
2012-03-01

And we are going to load data into tbl_Employee table in our production DB without using CDC for incremental load.

How to Do That
Here we are going to describe the main steps or logic to implement this scenario and we hope that you have well understanding with SSIS and no need to describe all the steps.

Step – 1 [ Preparing the Base Table in Source ]

Here we are altering our previous table and adding two extra columns in the name of InsertedDT and UpdatedDt with data type DATETIME. As we are not using CDC, these two columns represent the CDC works for us.
Whenever a new records is Inserted the InsertedDT and UpdatedDT is taking the GETDATE() values and whenever the records is Updated the UpdateDT columns is modified not the InsertedDT columns.

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 IDENTITY PRIMARY KEY,     
   EmpName        VARCHAR(50) NOT NULL,
   Designation    CHAR(1)     NOT NULL,
   DOJ            DATETIME    NOT NULL,
   InsertDT       DATETIME    NULL,
   UpdateDT       DATETIME    NULL
  );
GO

It is good it the Insert and Update method take care of the Last two columns i.e. InsertDT and UpdateDT.
 If not, we have to make a simple trigger in this table to take care of those columns.

How to prepare the Trigger

IF OBJECT_ID(N'[dbo].[trg_EmpDtlsInsert]', N'TR')IS NOT NULL
   BEGIN
       DROP TRIGGER [dbo].[trg_EmpDtlsInsert];
   END
GO

CREATE TRIGGER [dbo].[trg_EmpDtlsInsert]
ON [dbo].[tbl_EmployeeDetails]
AFTER INSERT
AS
BEGIN
DECLARE @v_Empid  INT;

SELECT * INTO #temp_Inserted FROM inserted;

IF EXISTS(SELECT * FROM #temp_Inserted)
      BEGIN
            SET @v_Empid = (SELECT EmpID FROM #temp_Inserted);
                   
            UPDATE [dbo].[tbl_EmployeeDetails]
                  SET InsertDT = GETDATE(),
                        UpdateDT = GETDATE()
            WHERE EmpID = @v_Empid;
                       
      END
END
GO

IF OBJECT_ID(N'[dbo].[trg_EmpDtlsUpdate]', N'TR')IS NOT NULL
   BEGIN
       DROP TRIGGER [dbo].[trg_EmpDtlsUpdate];
   END
GO

CREATE TRIGGER [dbo].[trg_EmpDtlsImsertUpdate]
ON [dbo].[tbl_EmployeeDetails]
AFTER UPDATE
AS
BEGIN
DECLARE @v_Empid  INT;

SELECT * INTO #temp_Inserted FROM inserted;

IF EXISTS(SELECT * FROM #temp_Inserted)
      BEGIN
            SET @v_Empid = (SELECT EmpID FROM #temp_Inserted);

            IF UPDATE(EmpName) OR UPDATE(Designation) OR UPDATE(DOJ)
                  BEGIN
                        UPDATE [dbo].[tbl_EmployeeDetails]
                              SET UpdateDT = GETDATE()
                        WHERE EmpID = @v_Empid;
                  END
      END
END
GO

Now Insert Some Records

INSERT INTO [dbo].[tbl_EmployeeDetails]
  (EmpName, Designation, DOJ)
VALUES('Joydeep Das', 'A', '2012-09-01');
GO

INSERT INTO [dbo].[tbl_EmployeeDetails]
  (EmpName, Designation, DOJ)
VALUES('Deepasree Das', 'B', '2011-07-01');
GO

SELECT * FROM [dbo].[tbl_EmployeeDetails];

EmpID
EmpName
Designation
DOJ
InsertDT
UpdateDT
1
Joydeep Das
A
2012-09-01
2015-11-11 21:22:41.043
2015-11-11 21:22:41.043
2
Deepasree Das
B
2011-07-01
2015-11-11 21:22:41.070
2015-11-11 21:22:41.070

Destination Table in Production DB

IF OBJECT_ID(N'[dbo].[tbl_Employee]', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_Employee];
   END
GO

CREATE TABLE [dbo].[tbl_Employee]
  (
   EmpID          INT         NOT NULL PRIMARY KEY,  
   EmpName        VARCHAR(50) NOT NULL,
   Designation    CHAR(1)     NOT NULL,
   DOJ            DATETIME    NOT NULL
  );
GO

Now the preparation is over and we are going to make the SSIS Package.       
                 
Step – 2 [ Introducing the Table to Track the Last Execution Date of Package ]

Here we introduce a new table which will track the Last execution date and time of the package so that if any data modification happens after that date time we must sink it with production table.

IF OBJECT_ID(N'[dbo].[tbl_ExecutionTracker]', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_ExecutionTracker];
   END
GO

CREATE TABLE [dbo].[tbl_ExecutionTracker]
  (
   Descr          VARCHAR(50) NOT NULL PRIMARY KEY,  
   LastExecDt     DATETIME    NOT NULL
  );
GO

-- Insert Records one day old to run the package
INSERT [dbo].[tbl_ExecutionTracker]
   (Descr, LastExecDt)
SELECT 'LastExec' As Descr, GETDATE()-1 AS LastExecDt;
GO

SELECT * FROM [dbo].[tbl_ExecutionTracker];

Descr              LastExecDt
LastExec         2015-11-10 21:37:37.333

Step – 3 [ SSIS Package ]

The simple logic is that the Package Extract the Data from source Greeter then or Equals to the Last Exec Dt.
So 

UpdateDt  >=  LastExecDt


Control Flow Task

[A] Execute SQL 1:



Here we are showing you what we do in the Execute SQL task named Retrieving Last Execution Date.



In SQL Statement:
SELECT LastExecDt
FROM   [dbo].[tbl_ExecutionTracker]
WHERE  Descr = 'LastExec';

[B] Execute SQL 2

In SQL Statement:

UPDATE [dbo].[tbl_ExecutionTracker]
   SET LastExecDt = GETDATE()
WHERE Descr = 'LastExec';
GO

Data Flow Task



OLEDB source -1 [ Retrieving Records using Last Execution Date ]



Conditional Split



 Step-4 [ Run the Package ]

First time Run

SELECT * FROM tbl_ExecutionTracker;

Descr                     LastExecDt
LastExec               2015-11-10 21:37:37.333



SELECT * FROM  [dbo].[tbl_Employee];

EmpID   EmpName                        Designation            DOJ
1              Joydeep Das                      A                             2012-09-01 00:00:00.000
2              Deepasree Das                  B                              2011-07-01 00:00:00.000

Now see the Last Execution Date

SELECT * FROM tbl_ExecutionTracker;

Descr                     LastExecDt
LastExec               2015-11-11 22:43:46.743

Now Insert a New Record and Modify Old Records

INSERT INTO [dbo].[tbl_EmployeeDetails]
  (EmpName, Designation, DOJ)
VALUES('Sukamal Jana', 'A', '2009-01-01');
GO


UPDATE [dbo].[tbl_EmployeeDetails]
   SET EmpName = 'Soni Sethi'
WHERE EmpID = 1;
GO

SELECT * FROM [dbo].[tbl_EmployeeDetails];

EmpID
EmpName
Designation
DOJ
InsertDT
UpdateDT
1
Soni Sethi
A
2012-09-01
2015-11-11 21:22:41.043
2015-11-11 21:22:41.043
2
Deepasree Das
B
2011-07-01
2015-11-11 21:22:41.070
2015-11-11 21:22:41.070
3
Sukamal Jana
A
2009-01-01
2015-11-11 22:48:23.653
2015-11-11 22:48:23.653

Now run the Package again



Now see the destination table
SELECT * FROM  [dbo].[tbl_Employee];

EmpID   EmpName           Designation            DOJ
1              Joydeep Das         A                             2012-09-01 00:00:00.000
2              Deepasree Das      B                             2011-07-01 00:00:00.000
3              Sukamal Jana        A                             2009-01-01 00:00:00.000
           


Summary
The main thing in the package is how to retrieve data from source depending on execution date.




Hope you like it.





Posted by: MR. JOYDEEP DAS

1 comment: