SQL Server 2008 and 2008 R2 Integration Services – Managing Local Processes Using Script Task

SQL Server 2008 R2 Integration Services includes a number of predefined tasks that implement common administrative actions to help with data extraction, transformation and loading (ETL). While in a majority of cases they are sufficient to deliver required functionality, there might be situations where an extra level of flexibility is desired.

SQL Server 2008 R2 Integration Services includes a number of predefined tasks that implement common administrative actions assisting with implementing data extraction, transformation, and loading (ETL). While in a majority of cases they are sufficient to deliver required functionality, there might be situations where an extra level of flexibility is desired. Such needs can be satisfied by employing SSIS Script Task. In this article, we will describe how its versatility can be leveraged to extend the set of features offered by very straightforward but also relatively limited Execute Process Task.

In order to accomplish our objective, we will provide a sample code that executes synchronously two programs (netsh interface ip set dns "Local Area Connection" static and ipconfig /registerdns), whose purpose is to carry out dynamic host record registration to an arbitrarily designated DNS server. To make our example somewhat generic, we will pass the names of both executables and their command line arguments to the Script Task via SSIS variables.

Start by activating Business Intelligence Development Studio and creating 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 six variables named sProcess1, sArgs1, sProcess2, sArgs2, sProcess1Out, and sProcess2Out of String data type and Script Task scope, and assign to the first four of them the following values (adjust the name of the network interface and IP address of the DNS server to match their values in your environment):

sProcess1	Netsh.exe
sArg1		interface ip set dns "Local Area Connection" static
sProcess2	IPConfig.exe
sArg2		/RegisterDNS

Use context-sensitive menu of the Script Task to display its Editor dialog box. Designate Visual Basic .NET 2008 as the ScriptLanguage. Assign User::Process1, User:sArg1, User::Process2, and User:sArg2 as ReadOnlyVariables followed by User::Process1Out and User::ProcessOut2 as ReadWriteVariables. Click on Edit Script... command button to access the Visual Studio Tools for Applications 2.0 interface. To simplify coding, add the Imports System.Diagnostics statement at the top section of the code and populate the body of Public Sub Main():

Public Sub Main()
Dim oProcess As New Process
    oProcess.StartInfo.FileName = Dts.Variables("sProcess1").Value.ToString
    oProcess.StartInfo.Arguments = Dts.Variables("sArg1").Value.ToString
    oProcess.StartInfo.CreateNoWindow = True
    oProcess.StartInfo.UseShellExecute = False
    oProcess.StartInfo.RedirectStandardOutput = True
    Dts.Variables("sProcess1Out").Value = oProcess.StandardOutput.ReadToEnd.ToString
    oProcess.StartInfo.FileName = Dts.Variables("sProcess2").Value.ToString
    oProcess.StartInfo.Arguments = Dts.Variables("sArg2").Value.ToString
    Dts.Variables("sProcess2Out").Value = oProcess.StandardOutput.ReadToEnd.ToString
    Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
    MessageBox.Show("Problem with the process " & oProcess.StartInfo.FileName & ": " & ex.Message.ToString, "Error")
    Dts.TaskResult = ScriptResults.Failure
    If Not (oProcess Is Nothing) Then
    End If
End Try
End Sub

As you can see based on the content of the code presented above, we leverage methods and properties of the System.Diagnostics.Process and System.Diagnostics.ProcessStartInfo classes. In particular, we take advantage of FileName and Arguments properties to specify processes and their arguments (sProcess1, sProces2, sArg1, and sArg2) to be executed. CreateNoWindow property allows us to hide Command Prompt windows that would otherwise appear during runtime.

Setting ProcessStartInfo.UseShellExecute property to False creates a new process directly from executable file assigned to Process.StartInfo.FileName property, rather than relying on the operating system shell. While the latter approach has a number of advantages (such as the ability to launch an appropriate process based on its file type association or invoking shell operations such as printing), we have to choose the former in order to facilitate redirection of input, output, and error streams (which we employ when capturing output of the commands we execute and populate values of the corresponding SSIS variables).

By default, processes initiated via System.Diagnostics.Process.Start run asynchronously. In order to enforce their orderly execution, we employ the Process.WaitForExit method ensuring that both commands (Netsh.exe and IPConfig.exe) are executed in sequence, which is essential to the successful outcome of our script (since the host record registration needs to be performed after the DNS settings on the local computer have been changed). Unfortunately, we also have to forgo synchronous read of redirected standard error, since combining it with synchronous read from the standard ouput (which, in our case, takes the form of the Process.StandardOutput.ReadToEnd method) could lead to deadlocks. While this can be accomplished in asynchronous manner (by employing Process.BeginOutputReadLine method and defining a designated event handler, as described in MSDN Library), we provide equivalent functionality by handling exceptions with standard Try... Catch construct.

It is important to note that in addition to standard .NET Framework mechanisms like garbage collection, exception handling, or synchronous and asynchronous execution, which we have mentioned here, you also have at your disposal variety of process-specific features incorporated into System.Diagnostics.Process and System.Diagnostics.ProcessStartInfo classes.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles