Find max date in multiple columns in SSIS

Question

I have csv file source and it have 5 date columns . I need to create a new column which will have the latest date after comparing all the 5 columns .

Expample :

source ![cust id dt1 dt2 dt3 dt4 dt5 101]1

Answer 1

I can think of a couple of ways that it can be done.

The first is using pure SSIS tasks and data flows. Based on your source data, the easiest approach I can think of would be to use an Unpivot Transformation followed by an Aggregate Transformation to get the values you need. Un-pivot on DT1, DT2, DT3, DT4 and DT5 and then aggregate over the date column and group by the CustID field. This would achieve what you need.

I don't have a copy of SSIS in front of me right now, but you can find the documentation for Unpivot Transformation and Aggregate Transformation on Microsoft Docs.

Alternatively, if the data is in a table (or can be put into a table), then you could just write a SQL script and call that via an Execute SQL Task to do the unpivot and aggregation for you like this:

 SELECT CustID, MAX([Date]) AS [Date]
   FROM (SELECT CustID, DT1, DT2, DT3, DT4, DT5) AS p
UNPIVOT ([Date] FOR [Dates] IN (DT1, DT2, DT3, DT4, DT5)) u;

Then you can just return the row set back into SSIS if you need to. Either method would be fine for such a small data set but test them both and see which one performs better and is easier to maintain.