SSIS Incremental Load using Package History

Incremental Load using package History

1. Create Package History table in Warehouse:

CREATE TABLE [dbo].[PackageHistory](
 [PackageHistoryId] [int] IDENTITY(1,1) NOT NULL,
 [PackageName] [varchar](50) NOT NULL,
 [RunDateTime] [smalldatetime] NOT NULL,
 [SourceDateTime] [smalldatetime] NOT NULL,
 CONSTRAINT [PackageHistory_PK] PRIMARY KEY CLUSTERED
(
 [PackageHistoryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2. Create the variables as shown below:

3. Design ETL Package as shown below:

3. Config 'Load Max ETL Date' task as shown below:

 4. Load staging fact table.

5. Config 'Load Max ODS Date' task as shown below:

6. Create tasks to Load fact table. Select data whose modified/createddate > Max(RundateTime) in package history.

7. truncate Staging table.

8. Config 'Insert Package History' task as shown below:

8. When the package is run, the data whose created/modified dates are greater than Max(RundateTime) in package history is loaded in to warehouse.