SSIS: Catch ExecuteProcess Task Output

In my SQL Server Integration Services (SSIS) projects, I often use ExecuteProcess tasks for various purposes.  I use them to kick off batch files (often batch files that were created earlier in the project) and command-line utilities such as WinZip and WS_FTP Professional.

However, it recently dawned on me that I had gotten in a very bad habit regarding how I used these tasks.  I never took the time to capture and log the output of the ExecuteProcess task.  This output is often valuable for troubleshooting runtime errors.  Capturing it and logging it is so easy that there really is no excuse not to do so.

All that you need to do to capture the standard output from your process it to assign a string variable to the StandardOutputVariable property of the ExecuteProcessTask.  That’s it.  Then you have the output and can log it as you wish (e.g., pass it to a Script task and use the FireInformation method of the IDTSComponentEvents interface).  While you are at it, do the same for the StandardErrorVariable property of the ExecuteProcessTask.  Now, should you need to troubleshoot an issue with your task, you should have all of the information that you should need.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: