Exploding rows - many to one lookup

Given a many to one relationship between an incoming flat file and a SQL table, is there a way to have explode the number of rows in the data stream?

Basically, I have a flat file and for every row in it there are one or more related rows in a SQL table that need to be looked up to provide a descriptive column and then passed down stream to the next transform. When I tried the lookup transform, SSIS only passed the descriptive column from the first matched row.

Thanks,

Danny

Danny,

Consider using a MergeJoin between the (sorted) flat file rows and the (sorted) SQL table.

The MergeJoin matches on a set of key columns and results in NxM output rows (in your case 1 X Many)