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%