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 10.10.10.10
:
netsh interface ip set dns "Local Area Connection" static 10.10.10.10
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).