Mindmajix

Incremental load in SSIS with example

Incremental load in SSIS

   Screenshot_53

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 →

         Screenshot_54

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

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:

 

bids-level

Data source:

Server name: Local host

Database: DB-MSBI  → Click next →

Screenshot_2

 

[ ⁄ ] Copy tables or views

[ ⁄ ] Write a query to retrieve the data from data set

 

Screenshot_3

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:

ssms-level

Enroll for Live Instructor Led Online MSBI Training


 

 

0 Responses on Incremental load in SSIS with example"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.