SQL Server 2008 and 2008 R2 Integration Services - Starting Local Processes

Monday Feb 14th 2011 by Marcin Policht

SQL Server 2008 R2 Integration Services offers a variety of methods that allow you to extend the range of its capabilities beyond those traditionally associated with data management.

Just like its predecessors, SQL Server 2008 R2 Integration Services offers a variety of methods that allow you to extend the range of its capabilities beyond those traditionally associated with data management. These auxiliary features tend to be extremely helpful when automating tasks supporting extraction, transformation and loading (ETL) activities. In this article, we will describe a fairly common example of such functionality that makes it possible to start local Windows processes directly from SSIS packages.

The most straightforward approach that provides the ability to launch an arbitrary program, a script, or a batch file leverages Execute Process Control Flow task. In order to examine its characteristics, we will create a package that triggers execution of a simple VBScript-based code. In the course of the presentation, we will modify its content to take advantage of different configuration settings available within the task. Its purpose will be to assign the local computer's primary DNS server and re-register its host record following the change. In general, these objectives can be accomplished by running the following two commands (assuming that the network interface is named Local Area Connection and the target DNS server has the IP address of

netsh interface ip set dns "Local Area Connection" static
ipconfig /registerdns

For the sake of simplicity, we will use a VBScript-based code to carry them out (obviously it would be possible to utilize .NET Framework to deliver equivalent functionality). Our sample script employs WshShell.Run method, with one mandatory (strCommand, which represents the command you want to execute) and two optional (intWindowStyle, which dictates the appearance of the Command Prompt window and bWaitOnReturn, which determines whether the script should wait for completion of the program execution before proceeding to the next statement) arguments (you can find more information about the syntax and capabilities of this method in MSDN Library). We also specify that the IP address of the DNS server will be provided as the first (and only) argument during the script invocation, by leveraging WScript.Arguments collection (if you were to implement the same feature using Visual Basic.NET code, you would need to reference My.Application.CommandLineArgs property, as documented in Visual Studio 2008 online documentation). Note that the script does not include any error checking (which you should provide if you are planning on deploying it in production environment).

Option Explicit
Dim oShell, sNetshCmd, sDNSIPAddress, iWindowStyle, bWaitOnReturn

sDNSIPAddress = WScript.Arguments(0)
iWindowStyle = 6	'hides the current window
bWaitOnReturn = True	'script execution halts until the program completes

Set oShell = WScript.CreateObject("WScript.Shell")
sNetshCmd = "netsh interface ip set dns " & Chr(34) &_
		"Local Area Connection" & Chr(34) & " static " & sDNSIPAddress
oShell.Run sNetshCmd, iWindowStyle, bWaitOnReturn
oShell.Run "ipconfig /registerdns", iWindowStyle, bWaitOnReturn
Set oShell = Nothing

With the script saved in a local folder, activate Business Intelligence Development Studio and create a new project based on the Integration Services template. Drag the Execute Process Task icon from the Toolbox and drop it on the Designer interface. Display its Editor dialog box and switch to the Process section, where majority of configuration settings are located. Assign the value of Executable property to the fully qualified path of the VBScript file you just created. Next, in the Arguments text box, type in the IP address of the DNS server. Confirm your choices by clicking on OK command button and execute the package to verify that it is functioning properly.

An alternative method of configuring the Execute Process task involves use of SSIS variables. In order for this approach to work properly, we need to first modify the script by changing the way the value of IP address of DNS server is obtained. Rather than relying on the WScript.Arguments collection, we will instead take advantage of WScript.StdIn.Readline method (which you can read about in TechNet Library Script Center). To implement it, simply replace the sDNSIPAddress = WScript.Arguments(0) line with sDSNIPAddress = WScript.StdIn.ReadLine statement in the VBScript file we created earlier. If you were writing a Visual Basic.NET application, you could use Console.ReadLine method, which offers equivalent functionality (refer to MSDN Library for more information about its characteristics).

Next, define appropriate SSIS variable. To ensure that their scope is limited to the Execute Process task, select it first and then, in the Variables floating window, click on the left-most toolbar icon. Assign a name to the newly created variable (we will call it sProcessIn), set its type to String, and its value to the IP address of the DNS server. Repeat the same sequence of steps for sProcessOut and sProcessErr but without specifying their values.

At this point, return to the Process section of Execute Process Task Editor. Change its Executable entry to the full path of cscript.exe (by default located in the %windir%\System32 folder), set Arguments to /nologo followed by a single space and the full path to the VBScript file, and assign StandardInputVariable, StandardOutputVariable, and StandardErrorVariable to User::sProcessIn, User::sProcessOut, and User::sProcessErr (respectively). Note that you also have an option to create dependency between outcome of the task and the script return code (FailTaskIfReturnCodeIsNotSuccessValue), define what the SuccessValue is, specify the TimeOut (by default set to 0), and set the WindowStyle (Normal, Maximized, Minimized, or Hidden).

Executing the package should result in the same outcome, however if you run into problems, you have an option of setting a breakpoint via Edit Breakpoints item in the context-sensitive menu of the Execute Process task (for example, enable Break when the container receives the OnPostExecute event condition) and use the Watch window to view content of each of SSIS variables we defined (admittedly, User::sProcessOut is in this instance not very helpful, however its significance depends primarily on the way output of the script is generated).

See all articles by Marcin Policht

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved