In the recent articles published on this forum, we have described a couple of methods that allow you to launch arbitrarily chosen processes using SQL Server 2008 R2 Integration Services. While the scope of the first of them (relying on Execute Process Task) was restricted to the local computer, the second one (leveraging custom .NET code incorporated into Script Task) is capable of extending this functionality to remote systems. We will provide here an example demonstrating such capability and discuss caveats regarding its implementation.
.NET Framework facilitates interprocess communication necessary to accomplish our goal through Windows Communication Foundation and remoting; however, considering our somewhat limited requirements, it is more straightforward to deliver the desired objective by employing Windows Management Instrumentation. Its features, exposed through System.Management namespace, are intended specifically for automating administrative tasks. We will utilize its Win32_Processs class in order to invoke a designated executable on a target computer (in our example, we will call GPUpdate.exe that triggers processing of Group Policy by Client Side Extensions, but obviously it is possible to apply the same approach to carry out other types of tasks that involve running non-interactive programs).
To implement our solution, launch Business Intelligence Development Studio and create a new project based on the Integration Services template. Drag the Script Task icon from the Toolbox and drop it on the Designer interface. With the newly generated task highlighted, activate the Variables window. Define two variables named sComputer and sCommand of String data type and Script Task scope. Assign to the first one the name of a remote computer where the executable will be running and set the other one to GPUpdate.exe /Target:Computer /Force.
Use context-sensitive menu of the Script Task to display its Editor dialog box. Designate Visual Basic .NET 2008 as the ScriptLanguage. Specify User::sComputer and User:sCommand as ReadOnlyVariables. Click on Edit Script... command button to access the Visual Studio Tools for Applications 2.0 interface.
In the Project Explorer window, toggle the Show All Files button to display References node. Use Add References... option from its context sensitive menu to display the corresponding dialog box, locate System.Management entry in the list appearing on its .NET tab, and click on OK command button to add it to your project. If you receive No template information found. See the application log in Event Viewer for more details message at this point, execute Program Files (x86)Microsoft Visual Studio 9.0Common7IDEVSTA.exe /InstallVSTemplates from the Command Prompt window (alternatively, you could also open Package.dtsx file and edit its content directly by adding <Reference Include="System.Management" /> entry in the <ItemGroup> element of the section that includes project references).
Once these steps are completed, type in Imports System.Management entry in the top section of the General Declarations area and populate the content of the Public Sub Main() with the following code:
Public Sub Main()
Dim sComputer = Dts.Variables("sComputer").Value.ToString
Dim sCommand = Dts.Variables("sCommand").Value.ToString
Dim cConnOptions = New ConnectionOptions
cConnOptions.Impersonation = ImpersonationLevel.Impersonate
cConnOptions.EnablePrivileges = False
Try
Dim oMgmtScope = New ManagementScope("\" + sComputer + "rootcimv2", cConnOptions)
Dim oProcess = New ManagementClass("Win32_Process")
oProcess.Scope = oMgmtScope
Dim cInParams = oProcess.GetMethodParameters("Create")
cInParams("CommandLine") = sCommand
oMgmtScope.Connect()
Dim cOutParams = oProcess.InvokeMethod("Create", cInParams, Nothing)
MessageBox.Show("Process ID " & cOutParams("processId"), "Process Created")
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
MessageBox.Show(ex.Message.ToString, "Error")
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
To briefly summarize our code, we start by extracting values stored in the SSIS variables, placing them temporarily in sComputer and sCommand (primarily to improve readability). Next, we define an instance of ConnectionOptions class of System.Management namespace, which determines settings necessary to establish a WMI connection. This includes COM Impersonation mechanism and extra privileges on the target computer (disabled since our task does not require their elevation). Note that it possible to customize other properties of ConnectionOptions class, such as COM Authentication level or its security context (rather than relying on the account running the SSIS package), if the default ones do not satisfy your requirements.
Next, we instantiate an object of ManagementScope class that represents the scope of management operations, pointing to rootcimv2 WMI namespace on the remote computer. Since this is where the Win32_Process WMI class is defined, we use it to define its instance. We also assign the value of sCommand variable (which contains the name of executable and its command line switches) to CommandLine input parameter of its Create method. Finally, we establish the connection to the management scope and invoke the Create method with appropriate input parameters.
The successful process creation is indicated by a message box displaying identifier of the newly created process. While it is not possible to run program in this manner interactively, you can verify successful outcome of GPUpdate.exe by examining content of the Group Policy Operational log with the Event Viewer (in particular, search for events 4016 and 8004, which signify, respectively, start and completion of Group Policy processing).
It is important to note that ability to manage remote systems via WMI is dependent on a number of prerequisites. In particular, both authentication and security parameters (as defined by properties of ConnectionOptions class) must comply with DCOM requirements. In addition, connections need to be established using credentials that have sufficient DCOM and WMI-level permissions, as well as are not affected by User Account Control or Windows Firewall restrictions. For a complete list of factors that need to be taken into consideration in such scenarios refer to the MSDN Library article Connecting to WMI on a Remote Computer.
