Search code examples
sql-serverssisdata-warehousedataflowtask

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

Solution

  • 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