1

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

Doodle
  • 481
  • 2
  • 7
  • 20
  • 1
    You are trying to make a log table on the destination side,which records the time taken to run the package. Is that what you are trying to achieve? – AswinRajaram Aug 08 '18 at 04:52
  • Yes exactly. I wasn't able to to put that in words. I need to save the run time of the extract in a separate table. But I am not sure how to do that. – Doodle Aug 08 '18 at 05:07

2 Answers2

3

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

AswinRajaram
  • 1,519
  • 7
  • 18
  • Thats perfect. I'll try it out. I currently used the event handlers to do it. I am trying to get the current date into a variable at the start of the package. Can you tell me how can i do it? I have been trying to use @[System::StartTime] but that doesnt work. It shows me yesterday's time. I need this variable for delta loading. ANd if I use get date in expression task in event handler onpreExecute it return me the latest time instead – Doodle Aug 09 '18 at 01:27
  • What I use is a `getdate()` to load into the table. In the execute sql task, there is a stored procedure, in which I just load the current system time at the start of load and the same at the end. Simple. – AswinRajaram Aug 09 '18 at 01:49
  • I need to update the table in the end and not in the beginning. I have been able to get the time but its somehow picking it as 11:49:30 rather than 11:49:29!!! Will have to troubleshoot a bit more – Doodle Aug 09 '18 at 02:26
  • Which approach did you follow? The one that I suggested? – AswinRajaram Aug 09 '18 at 04:43
  • I cant follow that because I need to update the log table after the procedure is run. Before that I just need to save the start time in a variable – Doodle Aug 09 '18 at 04:52
  • 1
    For that also, you can follow the same method and use a stored procedure in an `Execute SQL Task` and the `out` parameter of the sp can be stored into a variable. – AswinRajaram Aug 09 '18 at 05:43
1

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.

RFerreira
  • 39
  • 1
  • 1
  • 6