Executing a BAT file in SSIS

I wanted to execute a batch file in SQL Server Integration Services, and I wanted to utilize two parameters from the Execute Process Task in SSIS: StandardOutputVariable and SuccessValue.

The reasons for using these two parameters is that I wanted to know if the external batch file failed or not. And I wanted to capture any error messages produced.

In SSIS, it is straightforward. Drag the “Execute Process Task” and select your .bat file.

For the StandardOutputVariable create a new string variable. For the SuccessValue specify 0.

Now, the tricky part is getting the SuccessValue to work with %ErrorLevel% from the bat file. To do that, make sure that the last line in your bat file is:

EXIT %ERRORLEVEL%

This will return the error level to the Execute Process Task. Otherwise, the process will always seem to succeed.

The other tricky part is getting an actual error message produced from the bat file. Apparently there are separate output streams produced by the bat file. So you have to use some redirection operators. I found this out via a blogger CrankyBit. Microsoft’s reference can be found here. Here is an example:

C:\WINDOWS\system32\ping.exe www.google.com >> mybat_log.txt 2>&1

Notice the 2>&1 at the end. That will send out the error message that you would normally see when executing in a command prompt.

So I used that to capture the error logs.

Finally, I echoed out a custom error message to send to the output variable. I can then use this variable to send out an email for notification.

So here is the complete bat file:

@Echo OFF

Echo ================== >> mybat_log.txt

Echo Start: %Date% %Time%

Echo.

Echo Start: %Date% %Time% >> mybat_log.txt

rem ==================================

rem 2>&1 sends the actual error message

rem http://technet.microsoft.com/en-us/library/bb490982.aspx

rem ===================================

C:\WINDOWS\system32\ping.exe www.google.com >> mybat_log.txt 2>&1

Echo ErrorLevel = %ERRORLEVEL% >> mybat_log.txt

IF %ERRORLEVEL% NEQ 0 (ECHO An error has occurred on the mybat.bat file.

Echo Please check the log file for more detail at D:\mybat_log.txt

) ELSE (ECHO The mybat.bat file ran successfully)

Echo.

Echo End: %Date% %Time%

Echo End: %Date% %Time% >> mybat_log.txt

Echo.

Echo —

Echo Sent from SQL Server SSIS Package

EXIT %ERRORLEVEL%

DTS Dynamic Properties and Global Variables

It’s been a while since I used Global Variables in SQL DTS. So here is a simple tutorial to:
Setup a Stored Procedure using Input Variables Dynamically from an INI file (or any other source available in Dynamic Properties Task)
(e.g. EXEC usp_test ?, ?)

1. Add a SQL Task and enter your SQL script e.g. EXEC usp_test ?,?
a) Click on “Parameters”
b) Click on “Create Global Variables” to create global variables depending on how many you need in your SP
c) Click OK
d) Assign each Global Variable to a Parameter.
e) Click OK

2. Assigning the Global Variables to values from an INI file:
a) Add a Dynamic Properties Task
b) Click on “Add”
c) Expand on Global Variables
d) Select a Global Variable and click on Value and then click on Set
e) On the Add/Edit Assignment window, select a value from an INI file
f) Repeat steps (b) to (e) to assign other Global Variables
f) Click OK and you’re done

Note: There are multiple ways to accomplish the tasks above. For instance, you can add new global variables when you first create a DTS package. Then add a Dynamic Properties task to assign values to the Global Variables. Finally, add a SQL task to execute the SP using input parameters. The global variables at this point will already be available.