Will MessageBox.Show cause the timeout issue on server side?

Question

I have a scheduled SSIS package with a script task in SQL Server Agent on our server. I did set the timeout for the SQL connection, and for some of the codes inside the Try block, it will throw the error, and there is MessageBox.Show inside the Catch block. If I leave the codes as they are, it will fail the job, but if I comment out those MessageBox.Show and leave the Catch block blank just for testing purpose, the job ran successfully.

Anybody knows that the MessageBox.Show will affect the timeout for connection on server side or what exactly cause this different result after disable showing the error message?

Thanks in advance :)

it's not clear, but if you show a message box, i would assume execution would pause until the message box is closed?! — Tanner, Apr 12 '17 at 13:38
I don't think MessageBox.Show would slow down and cause a timeout on server. Could you post your code sample so that we can get a little clarity. Also, I would suggest to repeat your tests a few times back to back just to rule out the possibility of coincidence :) — Akshay Mahajan, Apr 12 '17 at 13:39
Or MessageBox pended the script until it reaches the limit of the timeout? — LONG, Apr 12 '17 at 13:43
@Tanner, how could the message box get closed on server, is there any command like `continue` from the batch script? — LONG, Apr 12 '17 at 13:45
@LONG i don't understand why you are using message box, why not just log it silently somewhere? — Tanner, Apr 12 '17 at 13:47
@Tanner I am trying to log the errors instead. Just curious why it will give a different result after commenting out the message box, which cost me certain time to figure out which part let the job failed. — LONG, Apr 12 '17 at 13:49

billinkc · Accepted Answer · 2017-04-12T13:55:46.750

In DTS, you could create UI interactions that would result in a package waiting indefinitely for a button click to continue.

SSIS attempts to redress this issue by determining whether it is running in "interactive mode". This is similar to Hadi's answer but different in that running an SSIS package outside of BIDS/SSDT/Visual Studio alone is not sufficient to trigger the interactive mode flag to be unset.

An attempt to interact with the UI from an SSIS package running in non-interactive mode will result in an Exception being thrown from the code.

If I am adding message boxes to a Script Task, I find a helpful pattern is to add the System::InteractiveMode variable as a ReadOnly variable to the Task and then use the following

bool interactiveMode = (bool) Dts.Variables["System::InteractiveMode"].Value;
if (interactiveMode)
{
    // UI code here
    MessageBox.Show("Something happened");
}

I find the above to be cleaner in that the same code can be deployed to production and also run on my desktop versus "Everything works and one second while I make changes to remove my debugging shims" ... and fix that little bit of code that could have been done better

As a final bit, I also find that using UI elements bad form because I'm lazy and don't want to have to screenshot or write down what they say. Instead, make use of the Dts.Events.FireInformation event in your package. For example, this generic code will enumerate all the variables (that I've checked as readonly or readwrite) and their values.

    public void Main()
    {
        bool fireAgain = false;
        string message = "{0}::{1} : {2}";
        foreach (var item in Dts.Variables)
        {
            Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
        }

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

Now when I run the package, my output tab in Visual Studio (where I can select it all) + the graphical results tab will have all of those information messages. When I run the package, if I'm using 2005/2008 or 2012+ with the package deployment model, I need to ensure I have the argument of /rep eiw to capture Errors, information and Warning events to the job log or console or whatever (the default is to only report Errors). 2012+ with project deployment model will automagically capture Information to the SSISDB.catalog.operation_messages table.

score 1 · Answer 2 · answered Apr 12 '17 at 13:39

1

Messagebox.show will throw an exception if it is executed outside visual studio ssdt. When you removed it, the catch block is empty so ur script will ignore the exception (empty catch block = no error handling, just ignoring).

If you removed the try.. catch block the exception will be thowed and a message will be shown.

The timeout maybe caused by the exception thrown by Messagebox.show

answered Apr 12 '17 at 13:39

Hadi

36,233
13
65
124

Thx Hadi, or could I understand that the `Messagebox.Show` will pend the task until it hit the timeout and then threw the error? – LONG Apr 12 '17 at 13:44
I think that. But the exception that `Messagebox.show()` throw is that ssis cannot show graphical component. Not the real one – Hadi Apr 12 '17 at 13:48

score 1 · Answer 3 · edited Nov 30 '18 at 00:40

I know this is old, but just in case it can help someone else with the same problem I had.

My package always worked in SSDT and gave useful log messages during error conditions. However, when it failed in production all I would get was the ambiguous "Exception has been thrown by the target of an invocation." message and nothing was logged.

It turns out I stupidly added a MessageBox.Show to my catch block before calling Dts.Log. I fixed this by implementing the "interactive mode" as billinkc described above.

Will MessageBox.Show cause the timeout issue on server side?

3 Answers3

Linked