Hi,

In SSIS, under the workflow pane, each tasks can have precedence condition
that will tell if the task will be executed or not. That's really a great
feature to control the task flow.

I would like to have this functionality in the DataFlow itself but data flow
it does not have any precedence condition. Does anyone have an idea of what
is the best practice to turn on/off part of my data flow conditionally?
Is it posdible to have a script component that would check my condition at
run time and either turn on off the next step in the data flow? I tried but
did not find any clue that way.

Best,

Francois Malgreve

Hello Francois,

The dataflow does not have the "On off" switch you describe.
You cannot turn off the next step in the flow.

The way I would do it is to break up my dataflow into multiple dataflows
so that I can use Precedence constraints.

Into the question I have read

"If a condition is met then do not execute the following piece in the data
flow"


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

I am not sure if I expressed myself properly but I meant that part of my
dataflow are "optional" depending of the setting of a global variable. Note
that that Variable could be computed from any kind of parameters, including
the data itself. This will not change what i explain later.

I don't think that what you propose would work as my second data flow would
need the "output data flow" from the first data flow. As I don't think that
it is possible, Ii have been thinking of a "hack":
I could add a script component in my data flow to copy the value of that
global variable into each row (well that's not great but as i said, it's a
hack).
From there I could have a conditional split that will turn on/off branches
of my data flow. Indeed if a condition is not met, that specific output of
the conditional split will not have any data, thus turning off that branch
of the data flow.

What do u think about it? Sure it's not elegant but that's not really
important if there is no better solution. On performace point of view I
guess that it must be outweighted anyway by the rest of the dataflow then
the impact might not be that great.

Best,

Francois Malgreve.


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Hello Francois,

You could use a RAW FILE as the intermediate data stores. Conditional workflow
is still what I think you want. The conditional split will split data based
on a condition in the data. I guess you could use that and not consume the
output. Would that work for you? The problem here is the data is still processed
which means your flow still does some work even if it just throws it away


Allan

Hi Allan,

That's an interesting idea but I am not sure if on a performance point of
view writing into a file would be better that just disregarding rows in
memory. Well it might depend of the amount of data as well and as with any
performance thing, measurement is the only answer.

Also I guess that if the data flow is long and complex it's better to
separate it in different data flow then I would agree that the raw file
solution is probably the most elegant.

Anyway thanks a lot for your help.

Best regards,

Francois.

PS: Do you think it would make sense to have a precondition component to put
in the data flow that would have the same role as the precondition clause of
the work flow? If so, do you think it's possible ot create one? Would it be
a good idea?
Well this is a little bit off topic and if you don't have time to answer i
would understand. Thanks a lot for all the help you provided already.


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Hello Francois,

Again I think the pre condition component would be in the control flow.
It would then tell you whether the Data Flow task should run.

My point about disregarding rows is that the pipeline still had to run and
therefore you consume CPU cycles. Cycles that you did not need to run.



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com