SSIS script task not working when scheduled

Question

I cannot find a similar question. I have an SSIS package that contains a visual basic script task with the following line in it - msgbox("some text") . It runs fine from BIDS and manually executed from MSDB, but when I schedule it in SQL Server agent the package seems to fun fine until that point and completes. But the message box does not appear and none of the actually tasks after that run. The scheduled job reports complete and success. Can you point me to the right solution, I believe it would have something to do with the SSIS proxy account and its security but can't find anything. Does anyone know how to resolve this?

Here's a snapshot of my code. As you can see, I'm firing off lots of message boxes in an attempt to log what steps are working withing my package.

xworkbook = ExcelObject.Workbooks.Open("C:\xxx.csv") 
xworksheet = DirectCast(xworkbook.Sheets(1), Excel.Worksheet) 
MsgBox("csv") 
xworksheet.Range("B:B").Replace(What:=",", Replacement:="") 
MsgBox("replace 1") 
xworksheet.Range("B:B").Replace(What:=".", Replacement:="") 
MsgBox("replace 2") 
xworkbook.SaveAs("C:\xxx.xlsx", FileFormat:=51) MsgBox("saved")

Answer 1

I believe that the reason it won't work is that when you run the SSIS task as a scheduled job it doesn't run in the context of your account but rather the service account for the SQL Server Agent and the message box won't show for you. The messagebox isn't valid for a non-interactive task.

Answer 2

@jpw hit the nail on the head for "The messagebox isn't valid for a non-interactive task."

To make it work you either need to strip your message boxes out of your code or inspect the value of the boolean Variable System::InteractiveMode

Code approximately

If CBool(Dts.Variables("System::InteractiveMode").Value) = True Then
    ....
End if

Assorted references

• Microsoft SQL Server 2008 Integration Services Unleashed

• How do I disable interactive message boxes from inside an SSIS package?

Answer 3

You overcome this by changing your process so that no user intervention is needed. There is no reason you should ever have a message box in SSIS except for debugging.