CODEWITHSUNDEEP
sql-serverssisdata-warehousedataflowtask
Doodle
Doodle

Reputation: 479

Extract and load data from multiple sources SSIS

I am planning to create a data warehouse and load data using SSIS from oracle to SQL Server. The latency time for the DWH is 5 minutes.

I need to run a package every 5 minutes for update_date_time on the source is between the last update and current time. I am planning to create a new table in the target DB which records the time when the extract is run so that I can compare the time from that table.

Can you tell me how I can achieve this using SSIS?

If I use the below package, it updates the new table muliple times but I only want it to update that table once for every time the package is runenter image description here

Upvotes: 1

Views: 996

Answers (2)

AswinRajaram
AswinRajaram

Reputation: 1622

This is how I do it. Before and after the DFTs I include Execute SQl transformations (Execution_Init and Execution_complete) to write into the log table. In those there is a stored procedure which writes into a log table (which I created), the package name, start time (int the first one) and end time (in the second one) and other details as such. I think you can follow the same way.

enter image description here

Upvotes: 3

RFerreira
RFerreira

Reputation: 39

I think you are talking about delta load. Check this link: https://dwbi.org/etl/etl/53-methods-of-incremental-loading-in-data-warehouse

After you implement the Delta, just create a job to run your package in the time you need.

Upvotes: 1

Related Questions