Search code examples
ssisbidsdataflowtask

How to handle an empty query result on a DataFlow Task?

I'm iterating several years with a Dataflow inside a Foreach Loop, but there are some years that will not return any data, therefore they SSIS package fails, is there a way to handle this? To tell the package to ignore those blanks and keep executing?

Thanks,

Solution

  • One approach you can take if you don't want to touch how the data is being passed to the Foreach component:

    1. You may include an additional Execute SQL Task component inside the Foreach container that checks first if there is any data to process.

    2. Create a variable to which your SQL task will set the record count to, for example @cntProceed

    3. Set this component to run before your Dataflow task.

    4. Modify the precedence constraint (green arrow from your SQL task to your Dataflow task). Set the evaluation operation to Expression and Constraint, Value to Success, and Expression to @cntProceed != 0

    What this all does: Inside the foreach component/container it will first check if there exists any data to work with. That's what the additional component will do, and if there's data to use, it will proceed to the dataflow component. Otherwise, that "each" (or rather.. that one loop) will end and the Foreach will move to the next.