SSIS Fetch first latest record only

Question

Hi, I have one SSIS package which get data from txt file and to the destination sql table. Here below diagram shows the SSIS diagram to fetch the latest record based on VEHNO. Hence, I need to use aggregate function on max reqtimestamp group by VEHNO. However, when I have latest reqtimestamp with the same duplicate rows, it insert duplicate rows. How can I modify my SSIS package below to fetch first record only?

SSIS diagram:

Desired Output:

Answer 1

Hi @jn93 ,

You may use Sort Transformation to delete the duplicate value first.

And then use Aggregate to get the max value(latest timestamp), note that the maximum value returns the maximum value in a group. For more information, see MAX (Transact-SQL). In contrast to the Transact-SQL MAX function, this operation can be used only with numeric, date, and time data types.

aggregate-transformation

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.