Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.