Incremental load in SSIS
Daily data — (Stores) –> Table
(Granularity data)
- Maintain history [SCD]
- Direct loading
- 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?
And?
Click parameters:
Parameter 0: USER:SDATE
Parameter 1: USER:EDATE
FREE Demo Class From Industry Experts - Enroll Now.!! MSBI training
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:
Explore MSBI Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!
List of Related Microsoft Certification Courses: