What is the best most efficient performant way to do a Control break in SSIS?

I have an EDI file with Different Transaction types. I would like to read for a Header, capture some specific info on the header, and read for another specific tran type that comes after and capture additional info. I have a couple of ideas like tagging the records with a key and loading into two temp tables for matching later but that means I would have to do double processing. Maybe a conditional split for the two transaction types and then unioning them downstream, but not sure if the right records would be associated. Possibly tagging sequential key and writng to raw files and matching on the raw file keys downstream.

I have a lot of ideas but I am looking for the best proven practice here so i don't spin my wheels or have to go back and re-engineer later.

Thanks,

Larry

My original conditional split idea didn¡¯t work because there would need to be a one for one delay on the record processing. I though I could pull this off with the merge join built-in blocking but them I would have to sort and I could have possibly used the sequence numbers on the input file as a sort, but decided not too perhaps later. Not sure if your idea will work because the splits of the transaction types happen at the same time. There would be no way to associate the type 30 record with the correct type 20 record.

I'll have to ruminate on this one for a while.

Thanks for the suggestion.

LarryETL wrote:

My original conditional split idea didn¡¯t work because there would need to be a one for one delay on the record processing. I though I could pull this off with the merge join built-in blocking but them I would have to sort and I could have possibly used the sequence numbers on the input file as a sort, but decided not too perhaps later. Not sure if your idea will work because the splits of the transaction types happen at the same time. There would be no way to associate the type 30 record with the correct type 20 record. I'll have to ruminate on this one for a while. Thanks for the suggestion.

Let me tell you where I am at, I took Phils advice an added a script component and this worked great for the last record in the file since the post execute only fires once.

Is there a way to pass the value back to a variable every time a record goes through the script component

Maybe force the post execute event to fire.

If not, I can create a new output field, but how do I get that value into a variable

I want to see if I can get it to work this way before i attempt your suggestion.

I am pretty close I just need to get the value back into a variable, coming from the datastage world , I can't understand why this is so difficult in SSIS.

Thanks,

Larry

This won't work, unless I am unserstanding wrong. In my case every time a record comes through the dataflow I need to capture a datafield and right it to a variable and I mayor may not use the contents of this variable downstream depending on my logic.

Thanks,

Larry

Phil,

The issue is that the post execute only fires once. If I have a 1000 records on my file I need to be able to read a datafield on each of the 1000 records one at a time and update the variable 1000 times to be used further down in the dataflow 1000 times. I don't think this can be done in a script component. Can someone correct me on this if I am wrong.

In the meantime my work around was to dump the record types that I wanted into a temp table and use a stored procedure with a cursor to update a column with data from a previous record.

Thanks,

Larry