I am trying to get the Exception Message Box to work in a script task in SSIS.
I am using the example from http://msdn2.microsoft.com/en-us/library/ms166340.aspx almost verbatum.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.MessageBox
Public Sub Main()
'
' Define the message and caption to display.
Dim str As String = "Do you want to push to Production?"
Dim caption As String = "Zip Push"
Dim var As Variables
' Show the exception message box with Yes and No buttons.
Dim box As ExceptionMessageBox = New ExceptionMessageBox(str, caption)
box.DefaultButton = ExceptionMessageBoxDefaultButton.Button2
box.Symbol = ExceptionMessageBoxSymbol.Question
box.Buttons = ExceptionMessageBoxButtons.YesNo
'box.SetButtonText("Yes", "No", "Cancel")
If Windows.Forms.DialogResult.Yes = box.Show(CType(Me,Windows.Forms.IWin32Window)) Then
Dts.Variables("Production").Value = True
End If
'
Dts.TaskResult = Dts.Results.Success
End Sub
And yet all I get is the following error
Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.
Has anyone gotten this to work?
BTW: I am running SQL 2005 SP2
Just use MsgBox("...")
You don't even need to reference any assembly for it.
By the way, what are you trying to do here? SSIS is supposed to be a batch-oriented process, not interactive.
-Jamie
|||MsgBox works, but it's too limited. I wanted the added functionality of ExceptionMessageBox. Besides, my question was not "How do I NOT use ExceptionMessageBox?".|||
 S1monk wrote: |
| |
| MsgBox works, but it's too limited. I wanted the added functionality of ExceptionMessageBox. Besides, my question was not "How do I NOT use ExceptionMessageBox?". |
Was it? I've just re-read your original post and didn't see that question anywhere. All I saw was "Has anyone gotten this to work?"
I'm afraid I don't know why your code isn't working. What exactly do you want to do?
-Jamie
|||
| Jamie Thomson wrote: |
| |
| What exactly do you want to do? |
I second this question. Also Jamie's comment about SSIS being a batch utility, not an interactive one.
|||
What I would like to do is use ExceptionMessageBox to display a message and allow me to customize the buttons, which I believe you cannot do with MsgBox. The example I used is just a simplest case taken from http://msdn2.microsoft.com/en-us/library/ms166340.aspx to test the functionality. I cannot get it to run. All I get is
Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.
Any help would be greatly appreciated, but comments like use something else and SSIS is a batch utility are not helpfull.
|||
| S1monk wrote: |
| |
| What I would like to do is use ExceptionMessageBox to display a message and allow me to customize the buttons, which I believe you cannot do with MsgBox. The example I used is just a simplest case taken from http://msdn2.microsoft.com/en-us/library/ms166340.aspx to test the functionality. I cannot get it to run. All I get is |
I don't know for sure but my guess from the error message is that this simply cannot be done. The script task is not designed to be used interactively as you are attempting to do.
| S1monk wrote: |
| |
| Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'. Any help would be greatly appreciated, but comments like use something else and SSIS is a batch utility are not helpfull. |
If something isn't working then I would have thought suggesting an alternative was perfectly good advice. Likewise the advice about SSIS being a batch utility was intended to be helpful and, as I think your error message proves, this advice has been borne out to be true.
The question "What exactly are you trying to do?" was a lead-in to suggesting an alternative that WOULD work. Due to the very nature of SSIS, prompting users for input from INSIDE a package is not an appropriate thing to do - better to prompt them elsewhere and pass that information into the package so that it can act upon it dynamically.
I was trying to proffer some simple advice but obviously that advice is not appreciated hence I won't post on this thread again. I have no desire to help someone that doesn't value that help. Good luck in finding a solution to your problem.
-Jamie
|||
The type you are passing to the Show method is not a window. You have to catch a handle of the top window and pass it to this method. Perhaps, even null could work but it might create a weird effects (like popping up in the backround or something similar).
|||
The error is because Me is not a window, it does not implement IWin32Window, so the cast is invalid - CType(Me,Windows.Forms.IWin32Window)
You do not have a Form, because SSIS is just not aimed at being an interactive tool, which is the point others have tried to highlight.
You could get the same functionality with old MsgBox or System.Windows.Forms.MessageBox (same thing really), it supports Yes/No/Cancel if you wish.
Sorry for going so far off the question, but I thought it might help provide a solution.
|||
I found a way to make it work
Option Strict On
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.MessageBox
Public Class ScriptMain
Public Sub Main()
Dim str As String = "Are you sure you want to delete file 'c:\somefile.txt'?"
Dim caption As String = "Confirm File Deletion"
Dim win As Windows.Forms.IWin32Window
' Show the exception message box with Yes and No buttons.
Dim box As ExceptionMessageBox = New ExceptionMessageBox(str, _
caption, ExceptionMessageBoxButtons.YesNo, _
ExceptionMessageBoxSymbol.Question, _
ExceptionMessageBoxDefaultButton.Button2)
If Windows.Forms.DialogResult.Yes = box.Show(win) Then
' Delete the file.
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|||[Shaking head]
I just don't understand why you'd want to do this. I just don't understand........ SSIS is not an interactive tool - your solution only works when debugging SSIS.
[/Shaking head]|||I don't understand why you are all so hung up on SSIS being batch or interactive. It is what it is and it works how you use it. We do a lot of data loads on one server and then push the data to different environments (Development, QA, Staging, Production). With DTS we had a seperate package for each push. It leads to a LOT of packages. My goal was to clean this up a little and make the packages a little more versatile. I don't know what flavor of SSIS you are using, but the above code works just fine in my INTERACTIVE production environment.|||
| S1monk wrote: |
| |
| I don't understand why you are all so hung up on SSIS being batch or interactive. It is what it is and it works how you use it. We do a lot of data loads on one server and then push the data to different environments (Development, QA, Staging, Production). With DTS we had a seperate package for each push. It leads to a LOT of packages. My goal was to clean this up a little and make the packages a little more versatile. I don't know what flavor of SSIS you are using, but the above code works just fine in my INTERACTIVE production environment. |
HAHAHAHA. Yeah, okay.|||I hope we can agree then, that when developing, testing or maintaining in BIDS, dialogs can be useful.
The use of dialogs IN SSIS packages is not a good idea in general, but even so, that's not a universal truth (dialogs + SSIS = misunderstanding SSIS and its intent) .
As long as the System::InteractiveMode variable is checked in advance, dialogs are fine. SSIS provides this variable since a good amount of time is spent in development and maintenance inside of BIDS, where its nice to make changes to variables without changing package source code.
Its for this same reason ("debugging and maintenance") that many shells and language interpreters have both an interactive mode ( bash, python,ruby, and powershell come to mind ) and may be run interactively as well as non-interactively.
