2 Data Flow Tasks Linking to one Execute SQL Task

Question

I have two Data Flow tasks where only 1 of the 2 will execute. After either of these execute I want an Execute SQL Task to execute. When I have a green from each data flow task going into the execute sql task, the execute sql task does not get executed. The data flow task completes successfully but execution never makes it to the Execute SQL Task. Can you only have one green arrow (success) going into a task? Thanks

What are you doing to make one Data Flow run and the other not? Using the Disable property? Attempting a quick reproduction with my answer and want to make sure I'm doing what you're doing. A picture of the control flow probably wouldn't hurt. — billinkc
You'll probably be wanting to change the Precedent Constraint from Logical And to Logical Or (double click the connector between the DFT and Execute SQL Task) — billinkc
Before the data flow task I check the value of a parameter that will be passed in to the package. I build the where clause of the sql statement based on the value of that parameter. For example, I run the package from command line and pass variable in like this: /SET \Package.Variables[User::gstrAdHocOrBau].Properties[Value];"AdHoc" — user1408685
It does appear to work using Logical Or instead. That would make sense because one or the other condition going into the execute sql task is true, if that is what is occurring. — user1408685

billinkc billinkc · Accepted Answer · 2015-02-12T14:20:43

By default, two tasks are constrained by the Constraint of Success. That is, the first thing must generate a success value before the second thing will get the signal to start.

You have a binary set of Data Flow Tasks feeding into a single Execute SQL Task. The Execute SQL Task is waiting for both to finish. That's not going to happen so you must change the constraint.

The simple way to do this is to double click on the line connecting the Data Flow to the Execute SQL, doesn't matter which, and change the Precedence Constraint from a "Logical AND" to a "Logical OR". This allows the Execute SQL Task to run if either Data Flow generates a Success.

The Biml construct for creating the Or constraint appears as follows. I specify in the PrecedentConstraint a LogicalType of Or

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="so_28463621">
            <Tasks>
                <Dataflow Name="DFT 1"></Dataflow>
                <Dataflow Name="DFT 2"></Dataflow>
                <Container Name="OneOrTwo">
                    <PrecedenceConstraints LogicalType="Or">
                        <Inputs>
                            <Input OutputPathName="DFT 1.Output"></Input>
                            <Input OutputPathName="DFT 2.Output"></Input>
                        </Inputs>
                    </PrecedenceConstraints>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

2 Data Flow Tasks Linking to one Execute SQL Task

2 Answers