Sunday, 2 September 2012

Using OR for multiple constraints into an SSIS task

After helping out a colleague a while ago with this issue, I thought I'd  post a short explanation of the gotcha that occurs when you route two or more precedence constraints (ie the 'arrows' that join tasks)  into a downstream task. The behaviour of the flow may not be what you expect. Hopefully a quick example will make this clear.

Step 1:

Create a package in your favourite version of SSIS (I'm using 2012 - but any will do). Create a Connection Manager to SQL Server (doesn't matter about the database), and add two SQL tasks to the Control Flow and connect them to a Script Task as follows:

Step 2:

On the first Execute SQL Task connect create a SQL statement that you know will succeed, such as SELECT 1.

Step 3:

On the second Execute SQL Task create a SQL statement that you know will fail, such as 'SELECT * FROM NoTable'

The Script Task can be left as is - we're just using to test the control flow logic.

Before you run the package - what do you think will happen? Will the script task execute?

Step 4:

Run the package. You should see the following:

If you thought that the Script Task wouldn't run - award yourself a pat on the back. If you thought it would - read on!

Step 5:

The default behaviour for precedence constraints is that all constraints must evaluate to True before allowing 'downstream' tasks to execute.

Therefore in order to ensure that the downstream script task executes, double-click on one of the constraints (it doesn't matter which one) and in the editor click the radio button that says 'Logical OR. One constraint must evaluate to True'.

Click OK and you'll see the precedence constraints change to being dashed;

Step 6:

Now only one of the tasks will need to run successfully for the script task to run. Run the package and you'll note that the script task has indeed run;