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 * 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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.