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
add comment

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.