SSIS Control Flow – Troubleshooting Precedence Constraints

sql serverssdtssis

The control flow of my SSIS package is not behaving as expected.

What I want to achieve:

  1. Test if CLR is enabled on SQL Server using

    SELECT value
FROM   sys.configurations
WHERE  name = 'clr enabled'

  2. If value == 0, attempt to enable CLR and display message Server is configured.
  3. If value == 1, display message Server is configured.

If I setup my control flow to do only one or the other, it works.
enter image description here

This flow works as expected. However, I would like to, if value == 0, continue to the MessageBox after Enable CLR on Server.

I tried changing the control flow to this
enter image description here

Now, what happens is this:

  • value == 0 [CLR Enabled on Server] hands control to [Enable CLR on Server], which completes and exits.
  • value == 1 [CLR Enabled on Server] completes and exits.

[MessageBox- Server is Configured] is never reached.

Can anyone help me understand this, and/or point me to a good resource about SSIS Conditional Control Flow?

Best Answer

Your precedent constraint defaults to an AND situation. The preceding task must return success and the expression must be true.

In binary scenarios like this, only one of those two tasks is ever going to happen. By adding the next level of constraint in - both tied to the message box step, the final step is waiting for the "Enable CLR on server" to be successful as well as "CLR Enabled on Server" to be successful and for the value to be equal to 0.

Since only one of those situations will work out, you need to swing the PC to be OR situation

enter image description here

That results in the green dotted lines instead of solid

enter image description here

As an FYI, in your Script task, assuming this runs in an automated fashion (SQL Agent), since you're using a message box, it will fail. There's a System level variable, System::InteractiveMode which is a boolean. It indicates whether you're running in an environment where you can interact with the desktop or not. If that evaluates to false and you attempt any GUI activity, the package will error out.

I find it helpful to add code like the following

public void Main()
{
    // TODO: Add your code here
    bool interactiveMode = (bool) this.Dts.Variables["System::InteractiveMode"].Value;
    bool fireAgain = false;
    string message = "This is some message I likely built out using other SSIS Variable values";

    if (interactiveMode)
    {
        MessageBox.Show(message);
    }

    this.Dts.Events.FireInformation(0, "CLR Check", message, string.Empty, 0, ref fireAgain);

    Dts.TaskResult = (int)ScriptResults.Success;
}

There I have a check against my interactive mode and only attempt to display something if I can. I also go ahead and fire an OnInformation event because that's automatically logged in the 2012+ Project Deployment Model, shows in both the GUI and the Output panel in Visual Studio/SSDT/BIDS and is easily recorded from DTEXEC.exe call by appending I to the reporting parameter i.e. /rep EI

Related Question