RUN NET START/STOP MSSQLSERVER FROM SSIS PACKAGE

HELLO,

I want to create a package which start and stop the SQL server's services... i know i can achive this via NET COMMAND.... but i coudnt find in which task (SSIS) I can place that command?..

I also came across that I can achieve this using Execute Process task but for this I have to define executable file.... actually i dont want ne thing outside from my SSIS package

CAN I ACHIEVE THIS WITH IN SSIS PACKAGE?

is there ne other alternative?

regards,

Anas

Execute "cmd.exe" and use the following arguments:

"/C net send ..."

Try running from the command line to see what I'm talking about.

cmd.exe /C net send ...|||

Phil Brammer wrote:

Execute "cmd.exe" and use the following arguments:

"/C net send ..."

Try running from the command line to see what I'm talking about.

cmd.exe /C net send ...

what do u mean by that? where shud i run that command? can u please explain it in more details...

regards,

Anas

|||In the execute process task. The executable is "cmd.exe" and the arguments are "/C net start..."

|||You can start and stop services using the WMI connection manager. The WMI connection manager's acquires a ManagementScope. Other connection manager's acquire SqlConnections, MessageQueues, and so on.

Add a WMI connection manager, named "WMI" with the following connection string, built dynamically as the case may be.

ServerName=\\localhost;Namespace=\root\cimv2:Win32_Service.Name="MSSQLSERVER";UseNtAuth=True;UserName=;

Use the connection manager in a task (custom or script) to invoke any management call (including service management). I left out handling of dependent services here (for example, SQL Agent is a dependent service), but this may suffice for the general concept.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Management ' Add reference to System.Management.dll

Public Class ScriptMain

Private wmiScope As ManagementScope

Private svc As ManagementObject

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

wmiScope = DirectCast(Dts.Connections("WMI").AcquireConnection(Nothing), _

ManagementScope)

wmiScope.Connect()

svc = New ManagementObject(wmiScope, wmiScope.Path, Nothing)

If CType(svc("State"), String) = "Running" Then

svc.InvokeMethod("StopService", Nothing)

End If

Catch ex As Exception

Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

|||

jaegd wrote:

You can start and stop services using the WMI connection manager. The WMI connection manager's acquires a ManagementScope. Other connection manager's acquire SqlConnections, MessageQueues, and so on.

Add a WMI connection manager, named "WMI" with the following connection string, built dynamically as the case may be.

ServerName=\\localhost;Namespace=\root\cimv2:Win32_Service.Name="MSSQLSERVER";UseNtAuth=True;UserName=;

Use the connection manager in a task (custom or script) to invoke any management call (including service management). I left out handling of dependent services here (for example, SQL Agent is a dependent service), but this may suffice for the general concept.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Management ' Add reference to System.Management.dll

Public Class ScriptMain

Private wmiScope As ManagementScope

Private svc As ManagementObject

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

wmiScope = DirectCast(Dts.Connections("WMI").AcquireConnection(Nothing), _

ManagementScope)

wmiScope.Connect()

svc = New ManagementObject(wmiScope, wmiScope.Path, Nothing)

If CType(svc("State"), String) = "Running" Then

svc.InvokeMethod("StopService", Nothing)

End If

Catch ex As Exception

Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

what do you means by "' Add reference to System.Management.dll"... actually i geting error on ManagementScope ((blue under line)

can u please tell me ?

thanx for ur support

regards,

Anas

|||

There are about 4 ways to add an assembly reference in a given script task. There may be others for all I know.

1. Project Menu/Add reference ... to System.Management.dll

2. View menu/Project explorer. Right click (get context menu) on the project explorer References node. Chose assembly...

3. View menu/Object Browser. Browse to System.Management assembly. Click on the "+" toolbar strip icon.

4. View menu/Class view. Right click (get context menu) on References node. Choose assembly...