Calling Other Executable in SSIS

Calling Other Executable in SSIS
By : Kasim Wirama, MCDBA, MVP SQL Server

Sometimes, you need to call other executable through SSIS, let say, you write custom business logic in executable, and you need SSIS to call the executable (out of process), bottom line here that you want the module is reusable without rewrite same business logic in SSIS. With the component that wraps the executable inside SSIS, you just focus on the executable, so easy and efficient results you can get with the component.

The component is called ExecuteProcessTask. The component in SSIS is enhanced compared to previous version of this in DTS. You can write error information to a variable and you can redirect output from the command file to a variable.

Some variable here you should to configure here.
RequireFullFileName property should be set to true if you require your package to know exactly the location of the executable.

Executable property should specify executable name, for example notepad.exe, it is better if you specify fullfilename.

Arguments property contains list of arguments that you want to pass into Executable property.

Working Directory property contains input for location from which you executable is executed from.

StandardInputVariable property contains values as input arguments for the executable.

StandardOutputVariable property will keep execution output into variable.

StandardErrorVariable property will keep execution error information into variable.

If you set true on FailTaskIfReturnCodeIsNotSuccessValue property, it will see value on SuccessValue property, if return value is same with value of SuccessValue property defined, so it is considered success, otherwise failed.

Timeout property defines in seconds how long the executable is expected to finish execution. Value 0 means wait indefinitely. This property works with TerminateProcessAfterTimeOut property.

WindowStyle property contains value Normal, Maximized, Minimized and Hidden. If you would like to avoid interaction the executable to end user, set it Hidden.