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 10.10.10.10 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 10.10.10.10 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
Try
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
oProcess.Start()
Dts.Variables("sProcess1Out").Value = oProcess.StandardOutput.ReadToEnd.ToString
oProcess.WaitForExit()
oProcess.StartInfo.FileName = Dts.Variables("sProcess2").Value.ToString
oProcess.StartInfo.Arguments = Dts.Variables("sArg2").Value.ToString
oProcess.Start()
Dts.Variables("sProcess2Out").Value = oProcess.StandardOutput.ReadToEnd.ToString
oProcess.WaitForExit()
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
Finally
If Not (oProcess Is Nothing) Then
oProcess.Close()
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.
