SSIS - Getting latest record of rows

Question

I'm trying to get the top record of the entities from the output of the script component, but I do not see any suitable component to achieve this.

For example:
Student ID          Date                Value
1                   2014-01-01          2
1                   2014-02-02          34
2                   2014-01-01          5
2                   2013-01-01          6
2                   2012-01-01          9

And I will get these:
Student ID          Date                Value
1                   2014-02-02          34
2                   2014-01-01          5

Something like the window functions would do this in SSMS. And this may need to be done within the data-flow. Any suggestion?

Answer 1

It can be done in ssis by joining the aggregation back to the source, it'll take a few steps but should work:

Answer 2

You can use the Data flow task then choose a correct destination(etc SQL,flatfile) to your task. Under the data access type. use a SQL statement