Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Dialogue box from SSIS's script task throw error when called through SSMS Job Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 3:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:03 PM
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.
Post #1416127
Posted Tuesday, February 5, 2013 5:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
Who do you suppose might interact with a Message Box when it is presented during an unattended execution of an SSIS package

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
Post #1416176
Posted Tuesday, February 5, 2013 5:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:03 PM
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.
Post #1416178
Posted Tuesday, February 5, 2013 5:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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


  Post Attachments 
StartJobsDialog.jpg (80 views, 19.27 KB)
Post #1416184
Posted Tuesday, February 5, 2013 6:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:03 PM
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.
Post #1416189
Posted Tuesday, February 5, 2013 6:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1416202
Posted Tuesday, February 5, 2013 7:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:03 PM
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..
Post #1416205
Posted Tuesday, February 5, 2013 8:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1416210
Posted Wednesday, February 6, 2013 12:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1416274
Posted Wednesday, February 6, 2013 5:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:03 PM
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"



  Post Attachments 
Capture.PNG (6 views, 36.89 KB)
Post #1416772
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse