Getting duration is seconds between datetime rows in SSIS

Question

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?

Answer 1

 CREATE TABLE MyTable(DateTime datetime, Events varchar(255), CF_ID INT)
 
 

GO

?

INSERT INTO MyTable (DateTime,Events,CF_ID) VALUES
('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 )

GO

4 rows affected

WITH CTE AS 
(SELECT   
  DateTime,
  Events,
  CF_ID,
   (DATEPART(SECOND, DateTime) +
   60 * DATEPART(MINUTE, DateTime) +
  3600 * DATEPART(HOUR, DateTime))
 AS ConvertToSeconds
FROM MyTable)

SELECT 
DateTime,
Events,
CF_ID,
LEAD(ConvertToSeconds) OVER( ORDER BY DateTime) - ConvertToSeconds

FROM CTE
ORDER BY DateTime
GO

DateTime                | Events | CF_ID | (No column name)
:---------------------- | :----- | ----: | ---------------:
2022-04-11 08:00:09.000 | Login  |   521 |               80
2022-04-11 08:01:29.000 | Inxt   |   426 |             1348
2022-04-11 08:23:57.000 | Rgal   |   731 |               11
2022-04-11 08:24:08.000 | hold   |    78 |             null

db<>fiddle here