Dialogue box from SSIS's script task throw error when called through SSMS Job

Question

Dialogue box from SSIS's script task throw error when called through SSMS Job

roohisachdeva2009

SSC Veteran

Points: 291
More actions
February 5, 2013 at 3:22 pm

#273896

I have got dialogue box added to SSIS package,through script at the end,that populates when process is completed.
But it throws the below error when called from SSMS job “Script Task: Error: Exception has been thrown by the target of an invocation”
Script has just below code is in C#
System.Windows.Forms.MessageBox.Show(“Process has completed”);
Please clarify.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Who do you suppose might interact with a Message Box when it is presented during an unattended execution of an SSIS package :unsure:

Your best bet is not to use message boxes for debugging, use logging, e.g. setup an Event Handler that logs all Informational messages to a table or a file. That way when the package goes to production to execute unattended you do not have these kinds of issues.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

roohisachdeva2009 SSC Veteran Points: 291 More actions · Answer 2

Thanks for your reply.

My SSIS package processes some files and user keeps the file in particular folder and execute that package through SSMS job which calls that SSIS package.

User wants to know through dialogue box that it job has got completed.

She does not want to receive any sort of emails or other notifications where she has to check it manually.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

When SSIS is kicked off from a server, like when running an Agent Job via SSMS, the package is actually running on the server not on the user's machine. What your user wants is sort of an option if your network and computers are configured in such a way that the Agent Job can issue net send messages to your user's computer, however the technique is a remnant of long gone days and I would not recommend trying it for various reasons.

When someone executes a job a dialog appears showing that the the job is running and it changes status when it completes. What is wrong with the user checking the dialog periodically to see when the job complete, or with receiving an email?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

roohisachdeva2009 SSC Veteran Points: 291 More actions · Answer 4

The problem here is user does not even run the job manually. She just double clicks the batch file which calls the job through Sqlcmd and then it calls the SSIS Package.

User-> Batch File(sqlcmd)->Job->SSIS Package

So she does not interact with job/SSMS also. She needs a little pop up which should tell her that job is completed.

Other way is I can write a loop in job which should wait until job completes and then display a customized message in command window.

But she does not want to run the batch file through command window even. She just need to double click the batch file and give her the dialogue box.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

In that case I would recommend you move towards a C# Windows Forms app. Anything done in Batch would be a workaround and I cannot bring myself to recommend new development be done in VBScript.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

roohisachdeva2009 SSC Veteran Points: 291 More actions · Answer 6

Well I am working around generating dialogue box while running batch file.I am working around that..your CODE 🙂 suggestion will be highly appreciated..

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

VBScript can do popups or download Visual Studio C# Express Edition and create a Windows Forms app.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Phil Parkin SSC Guru Points: 246961 More actions · Answer 8

The SQL Agent job runs asynchronously - so it's not going to return 'finished' info to your application.

You could, perhaps, implement an MSMQ solution, though that may be a sledgehammer to crack a nut.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

roohisachdeva2009 SSC Veteran Points: 291 More actions · Answer 9

Below is the solution I found,I tried to raise a dialogue box through batch file after completion of the job that processes all the files in a folder1 and move it to folder2 one by one after processing.

sqlcmd -S myserver -d "master" -Q "my_procedure"

:loop

ping 1.1.1.1 -n 1 -w 10000 > nul

IF EXIST "D:\folder1\*.csv" goto :loop

start %comspec% /c "mode 40,10&title Process Completion-Confirmation&color 1e&echo.&echo. Process has completed.&echo.&echo.Press a key!&pause >NUL"

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

Nice workaround. Thanks for posting back.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 11

I just learned about the possibility in a book I am reading and thought I would pass it along either for you or future readers looking for a way to show a popup from a shell script. This site shows it well:

http://techibee.com/powershell/how-to-get-pop-up-message-box-using-powershell/839

Granted, PowerShell is not setup by default (and arguably should never be) to allow double-clicking a script to invoke the execution of it the way Windows Batch us for security reasons but the code solution itself seems more elegant and wscript can be called from a Batch script just the same.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato