I have an event-based csv file that logs an event along with the time of execution. I need to be able to get the duration between the events by taking the DateTime row and subtracting the DateTime row below it to give me the total duration of that particular event in secs.
So take the sample table below:
Date/Time Event CF_ID EventDuration(s)
04/11/2022 08:00:09 Login 521
04/11/2022 08:01:29 Inxt 426
04/11/2022 08:23:57 Rgal 731
04/11/2022 08:24:08 hold 78
After transformation, it should look something like this:
Date/Time Event CF_ID EventDuration(s)
04/11/2022 08:00:09 Login 521 80
04/11/2022 08:01:29 Call 426 1348
04/11/2022 08:23:57 DAB 731 11
04/11/2022 08:24:08 hold 78
I can probably achieve this in SQL with relative ease, however, I need to be able to use an SSIS transformation to do this such as a derived column. Can this be achieved in SSIS?
4 rows affected
db<>fiddle here