Conditional Split Processing Order

#1

Hi -

I have a conditional split to process multiple record types in one
file. The file contains a trailer record that will obviously be the
last row. This record has some information in it that I need for the
Data Transform tasks of the other row types. Is there any way of having
the other tasks "wait" for the trailer to be processed before
executing?
Would I maybe need another Data Flow Task first to parse out the
trailer record, and then on completion run the rest of my Transforms?

Thanks

#2

Hello Todd,

Raw Files store data in a format that needs no conversion by the Source Adapter/Destination
adapter hence why it is quick to be read. It still has to touch disk though
so if you have a slow 4200 Disk Subsystem and a 10GB RAW File then you are
still going to be hanging around a while.

Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

Quote:

Not sure I understand your problem. What is this 'trailer record' and
what does it do? Does it get split out all by itself apart from the
other records?

If you end up using another Data Flow in your package, consider using
Raw File destinations for the split result sets. Then in the second
data flow, use Raw File sources to pick up where the first left off.
Raw files are supposed to be faster than any other type of connection
(so I have heard).

HTH

"bigbrorpi (AT) gmail (DOT) com" wrote:

Hi -

I have a conditional split to process multiple record types in one
file. The file contains a trailer record that will obviously be the
last row. This record has some information in it that I need for the
Data Transform tasks of the other row types. Is there any way of
having
the other tasks "wait" for the trailer to be processed before
executing?
Would I maybe need another Data Flow Task first to parse out the
trailer record, and then on completion run the rest of my Transforms?
Thanks

#3

Hello ,

If the Script Component is Synchronous then no there is no way to wait for
the last row to come by and do something with it. A Sync component as soon
as it has filled a buffer will flow through the component. An Async component
will allow you to effectively stall the pipeline and do what you want with
the data before you are ready to throw it out the other end.

The problem you will face here in processing the file is that the last row
will break the definition of the rest of the file won't it?

You could read the file in and have the row be one string. You then go to
a script component. Internally you tag the rows with a incrementing value.
Your script component has two outputs.

Data (Would need to crack the row)
Footer

You pipe the relevant data down each output. You can now Derive Columns
from the Footer or you could assign the footer values to variables in the
Script component. Which method you choose depends on how you want to use
the footer row.

This above is in the pipeline. You can of course do this in the Control
Flow using a Script task. Then i would be inclined to read to Variables
and remove the trailer from the file so it can be processed Synchronously
in the pipeline

Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

Quote:

True, you are still limited by the i/o. My point was that if you need
to cache some data to disk to pick up in another Data-flow and don't
need it for anything other than SSIS, then a Raw file will beat a text
file or SQL table, all else being equal.

I don't think either of us has answered the original question. Looking
at it again, I think I understand it a little better:

To the original author: try this:
1) set up your first data flow to split the incoming stream using a
conditional split.
2) send the bulk of it to a Raw File for later,
3) send the trailer row to (and I'm guessing here) a Script Transform
that
can read it and assign values to package scoped variables based on
field
values in the row.
4) create a second data flow and use a Raw Source to pick up the file
from
step 2
5) use the variables from step 3 in the second data flow to do what
needs to
be done.
NOTE: I say "guessing" because I have not used a Script Transform all
that much. Allan Mitchell may have more input on that one. He wrote
the book on SSIS (literally, ISBN = 0-7645-8435-9) and may be able to
give some additional pointers.

Discussions on Raw Files aside, are we answering your question?

"Allan Mitchell" wrote:

Hello Todd,

Raw Files store data in a format that needs no conversion by the
Source Adapter/Destination adapter hence why it is quick to be read.
It still has to touch disk though so if you have a slow 4200 Disk
Subsystem and a 10GB RAW File then you are still going to be hanging
around a while.

Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com
Not sure I understand your problem. What is this 'trailer record'
and what does it do? Does it get split out all by itself apart from
the other records?

If you end up using another Data Flow in your package, consider
using Raw File destinations for the split result sets. Then in the
second data flow, use Raw File sources to pick up where the first
left off. Raw files are supposed to be faster than any other type of
connection (so I have heard).

HTH

"bigbrorpi (AT) gmail (DOT) com" wrote:

Hi -

I have a conditional split to process multiple record types in one
file. The file contains a trailer record that will obviously be the
last row. This record has some information in it that I need for
the
Data Transform tasks of the other row types. Is there any way of
having
the other tasks "wait" for the trailer to be processed before
executing?
Would I maybe need another Data Flow Task first to parse out the
trailer record, and then on completion run the rest of my
Transforms?
Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Conditional Split Processing Order

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Conditional Split Processing Order - 12-06-2006 , 11:23 AM

RE: Conditional Split Processing Order - 12-06-2006 , 04:02 PM

RE: Conditional Split Processing Order - 12-08-2006 , 06:02 PM