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?

Can you explian more how to get the duration ? It is the duration between the current event and the previous one for the same date? — Amira Bedhiafi, May 06 '22 at 11:57
Yes correct, so as you can see from the above table of results, The LOGIN event started at datetime 08:00:09 and the next event CALL started at 08:01:29. This gives the duration of the LOGIN event 80 seconds (so the time between the events) — dragonfury2, May 06 '22 at 12:01

Amira Bedhiafi · Answer 1 · 2022-05-06T16:57:53.677

-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

edited May 06 '22 at 16:57

answered May 06 '22 at 13:36

Amira Bedhiafi

8,088
6
24
60

This is a great SQL solution, however, I'm looking for a solution using transformations in SSIS. Such as a derived column, with an output similar to what you have shown above. Also, it should output the results on the row above, not below. – dragonfury2 May 06 '22 at 15:38
If you are using a datasource that points directly to an SQL Server database, why not using a query instead of make it more complex using SSIS components? – Amira Bedhiafi May 06 '22 at 15:48
Unfortunately the data is in CSV format, and its part of a file reading component in SSIS. The SQL server option would have been ideal I agree. – dragonfury2 May 06 '22 at 15:51
1

If it must be done in SSIS - then I think you are going to need 2 sources. One for the current and one for the next - adding a row number to each source. The current will start each row number for each group at 1 - and start each group at 0 for next. Then you can use a merge join (inner join) on the row number. Once you have that it is a simple calculation of the difference between curr.datetime and next.datetime. – Jeff May 06 '22 at 20:27

Getting duration is seconds between datetime rows in SSIS

1 Answers1