sgeiger 71434 (7/19/2012)
I don't disagree with your statement that events get triggered, we write SSIS packages that handle and perform tasks based on events. The issue that I and others have with doing things like popping msg boxes is that you totally interrupt and stop the control flow until there is some intervention to continue it. I could not even imagine deploying a package with a message box to a production environment. On this we do disagree.
I understand your argument, I appreciate the logic behind it, and I am glad you have expressed and defended it.
The scenario you describe cannot easily occur in Production - at least not in SSIS 2008, 2008 R2, or 2012. This was an issue in SSIS 2005 RTM, but I believe (not certain) that it was addressed in a service pack. Calls to messagebox functions in a Production environment - executed from SQL Server Agent or any scheduler running as a service configured according to most versions of SQL Server and SQL Server Agent run-time best practices - should raise an error in the SSIS run-time (Code: 0x00000001 Source:
<Script Task Name> Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application.
). Interestingly, you can raise and display a message box from inside BIDS or SSDT and
by executing using dtexec from the command line. The aforementioned error is raised when the SSIS package is executed from teh security context of a service account (configured with default roles and permissions for service accounts).
As a best practice, unqualified calls to message boxes should be avoided. I address this in an upcoming article in this series - supplying one implementation of the Debug Pattern. But in a demonstration written for those new to SSIS, I have no issue using message boxes.
Message boxes are a tool, and all tools have their place in software development. Best practices exist, in my opinion, to warn developers of potential hazards and abuse when utilizing certain practices that lend themselves to misunderstanding, misapplication, and misuse. Your warning about message boxes is warranted (and again, appreciated), but it is also incomplete from an SSIS architecture perspective.
Thank you for the good discussion - it is appropriate for this thread. I would be happy to continue this conversation with you via email: email@example.com.
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics