Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
roohisachdeva2009
roohisachdeva2009
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
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
roohisachdeva2009
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
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
Attachments
StartJobsDialog.jpg (86 views, 19.00 KB)
roohisachdeva2009
roohisachdeva2009
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
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
roohisachdeva2009
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
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
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8414 Visits: 19517
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
roohisachdeva2009
roohisachdeva2009
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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"
Attachments
Capture.PNG (9 views, 36.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search