Mahipal Padigela's Blog

Start/Stop windows services from SSIS

We had a requiremnet to stop Reporting Services windows service(ReportServer) at the beginning of ETL load and start it at the end. I used a couple of Script tasks (one at the beginning and one at the end of SSIS Master Package) with code something like this:

drag a script task onto control flow and add the following code to stop the service

'stop ReportServer service script task
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ServiceProcess

Public Class ScriptMain

    Public Sub Main()
        Dim controller As New ServiceController
        controller.MachineName = "."
        controller.ServiceName = "ReportServer"

        Dim status As String = controller.Status.ToString

        If status.ToLower = "running" Then
            controller.Stop()
        End If

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

drag a script task onto control flow and add the following code to start the service

'start ReportServer service script task
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ServiceProcess

Public Class ScriptMain
    Public Sub Main()

        Dim controller As New ServiceController
        controller.MachineName = "."
        controller.ServiceName = "ReportServer"

        Dim status As String = controller.Status.ToString

        Do While Not status.ToLower = "running"
            Try
                controller.Start()
            Catch e As Exception
                controller.Refresh()
                status = controller.Status.ToString
            End Try
        Loop

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

Tags: SSIS and windows services, stop reportserver