May 28, 2010 at 10:09 am
I have created a package that basically stops the Report Server service while data is being loaded into a Data Warehouse once completed Restarts the service so reporting can continue. I have used 2 script tasks to accomplish the stopping and starting of the service and everything works as expected if run manually, however once set up as a job fails due to what I think is a security issue with the SQL Server agent account. I am not familiar with SQL security and would be grateful if anybody can point me in the correct direction. This is the code I have used in the script: -
Public Sub Main()
' Add your code here
'
Dim stopcontroller As New ServiceController
stopcontroller.MachineName = "."
stopcontroller.ServiceName = "ReportServer"
Dim status As String = stopcontroller.Status.ToString
If status.ToLower = "running" Then
stopcontroller.Stop()
Else : stopcontroller.Start()
End If
Dts.TaskResult = ScriptResults.Success
End Sub
Thanks in advance Dave.
May 28, 2010 at 2:47 pm
This is a fascinating idea.
What error are you getting from SSIS on the progress tab?
May 28, 2010 at 2:57 pm
I'm assuming your account has permissions to start/stop services on the machine which is why it works when you run it manually. Make sure the SQL Agent account has permissions to start/stop services also. This article may help:
May 29, 2010 at 9:21 am
Thanks for the reply and link, I will give it a go.
Dave.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply