I have a flat file source that contains a lot of duplicate records, which have the same key fields but differ by their "changed date". I need to split this up into two outputs, one for the "latest" and one for "everything else."
When doing this from a table-based source I can simply add a derived column, something like:
SELECT CASE WHEN EXISTS ( SELECT * FROM SomeTable t2 WHERE t2.KeyField = t.KeyField AND t2.ChgDate > t.ChgDate ) THEN 1 ELSE 0 END AS Historical FROM SomeTable t
I need to do something similar from a flat-file source, but I can't figure out how to do the subselect. I tried adding a derived column transformation, but it doesn't look like you can derive column values based on other rows in the rowset. I also tried a merge join transformation (an LEFT OUTER join would accomplish the same goal) but that doesn't see mto allow self-joins. The only transformation that seemed to work on the entire rowset is the aggregate one, and that only works with a pre-defined set of aggregates.
For now, I'm creating a temporary SQL table that holds the entire data set, then using further tasks to copy them out, but I would really like to find a way to avoid that. Is there any type of transformation I can use that can join or sub-query the current rowset in memory?