8

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?

2
  • 1
    Writing up an answer but the short is that you need to change the AND to an OR on those P/Cs coming off of "CLR Enabled on Server"
    – billinkc
    Oct 9 '15 at 12:26
  • 1
    And right you are! Man, I've been stuck on this for so long. Thank you so much. Keep writing, I still don't understand why my previous approach did not work.
    – krystah
    Oct 9 '15 at 12:29
11

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.