3

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")
billinkc
  • 59,250
  • 9
  • 102
  • 159
user2515978
  • 61
  • 1
  • 5

3 Answers3

3

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.

jpw
  • 44,361
  • 6
  • 66
  • 86
  • I think you are technically correct, but how do I overcome this. The actual code that doesn't work is as follows and I suspect it does not work for the same reason the msgbox does not appear. – user2515978 Jun 24 '13 at 12:03
  • How do I overcome this. The actual code that doesn't work is as follows and I suspect it does not work for the same reason the msgbox does not appear.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") – user2515978 Jun 24 '13 at 12:06
  • I don't think you can overcome this as the scheduled job runs in a non-interactive fashion. If you need to give it input during execution you need to run it interactively. – jpw Jun 24 '13 at 12:17
  • Shouldn't the proxy account for SSIS that I set run as my user and thus the vba work normally? What I am doing is no different that running a vba macro that manipulates excel data without ever showing the excel workbook. – user2515978 Jun 24 '13 at 12:28
  • 2
    The security context shouldn't be the problem, but rather that when you run the job using the SQL Server Agent (whether using proxy account or not) the job runs without interaction. When you ask for input using the message box there's no user there to show it to and get input from. – jpw Jun 24 '13 at 13:49
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

Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
2

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186