Tuesday, March 18, 2008

InteractiveMode Variable

In DTS, message boxes were a great way to debug packages and especially ActiveX scripts. In SSIS, other methods of tracking down issues, such as breakpoints and logging, are the way to go. In fact, message boxes aren't allowed in SSIS unless you are running the packages on a client machine. If you run a package on a server with the following code:

MsgBox("This is a message box!")

You'll see an error message similar to this:

Error: 2008-03-18 23:00:03.30 Code: 0x00000002 Source: Script Task Description: The script threw an exception: 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. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:02 PM Finished: 11:00:03 PM Elapsed: 0.546 seconds. The package execution failed. The step failed.

If you come across that one user that just can't give up their precious message boxes, you can use this code:

If CBool(Dts.Variables("InteractiveMode").Value) = True Then
MsgBox("This is a message box!")
End If

This will allow the user to see their message box on the client, but repress the error when running on the server!

Version: SQL Server 2005 SP2

1 comment:

Manoj said...

This is cool. Started using this and it reduced a lot of last minute pain to deploy a Package