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!!!!
