Day #64 - ETL for Data and Delta Data Management

Custom SSIS example sample for ETL setup for Data Extraction and Update

Scenario

• Two Databases (Source and Target)
• Example with Test Table with few columns
• Ability to get New Data
• Ability get Delta Data (Updates)

Step in SSIS Project

Step 1 - Create a Data Flow Task

Step 2 - Add connection managers for Source and Target Databases

Step 3 - The operators and layout is (Source Data -> Lookup in Target Database -> Insert / Update TargetDatabase)

Step 4 - OLEDB Data Source Settings

Step 5 -  Lookup to map for data

Step 6 - Lookup Mapping

Step 7 - Match Non-Matching for Insert / Updates

Step 8 - Match Destination Settings

Step 9 - Non Match Update Query

Step 10 - Non Match Update Params

Reference table script

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[Col1] [int] NULL,
[Col2] [int] NULL,
[Col3] [int] NULL
) ON [PRIMARY]
GO

Happy Learning!!!!