Incremental load in SSIS with example

Incremental load in SSIS

Daily data — (Stores) –> Table
(Granularity data)
  1. Maintain history [SCD]
  2. Direct loading
  3. ETL loading (indirect load)
Eg: loading the specified days data from one table to another table.
Sol: Go to SSIS → variable → click add and create two variables
Name                   Scope                   Data type                                Value  
EDATE               Package              Data time 6/2/2011                 12:36 AM
SDATE               Package              Data 6/2/2011                          12:36 AM
1) OLEDB source → rt click → edit →
OLEDB connection manager: local host:: DB-MSBI
Access mode: SQL command.
SQL command text:
Select * from Emp-Daily where St-Dt between?
Click parameters:
Parameter 0: USER:SDATE
Parameter 1: USER:EDATE

2) OLEDB destination → EMP-HIST table.
3) SSIS menu → package configurations → check enable package configuration → click add → next
Configuration type: XML configuration file.
Configuration file name: Browse and specify file new name click nextà
Go to SDATE and EDATE and check the ‘value’ sections.
Next → finish → close
Open configuration file → change SDATE and EDATE and run the package.

SSIS Import Export Wizard

It performs operations between database to database, database to file, file to file.
a) BIDS level:
Data source:
Server name: Local host
Database: DB-MSBI  → Click next →
[ ⁄ ] Copy tables or views
[ ⁄ ] Write a query to retrieve the data from data set
Now the system creates a package according to the settings given, execute the package and observe the result i.e., go to for DB new in databases and observe.
b) SSMS level:
                                        Enroll for Live Instructor Led MSBI TRAINING


Get Updates on Tech posts, Interview & Certification questions and training schedules