Scenario
RED can automatically generate Integration Services packages for Load tables, however in some situations it is a requirement to run an arbitrary Integration Services package that contains some specialised functionality or has been migrated from a legacy environment.
Solution
A Host Script can be created that calls the DTEXEC command line utility to run the package. The Host Script needs to check the output from DTEXEC to find whether or not the package succeeded and return an appropriate Return Code and Return Message to the RED scheduler.
This is an example script that can be used as a starting point for this purpose:
@ECHO OFF SETLOCAL ENABLEDELAYEDEXPANSION SETLOCAL ENABLEEXTENSIONS SET DTEXECPATH="C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" SET DTSXFILE=C:\temp\MyPackage.dtsx SET LOGFILE=C:\temp\MyPackage_%WSL_SEQUENCE%.log REM ********************************************************** REM ************* F I L E C H E C K *********************** REM ********************************************************** :LABEL_FILECHECK IF EXIST %DTSXFILE% goto LABEL_RUNPKG ECHO -1 ECHO Package file %DTSXFILE% was not found. EXIT REM ********************************************************** REM ************* RUN PACKAGE *********************** REM ********************************************************** :LABEL_RUNPKG %DTEXECPATH% /File %DTSXFILE% /Reporting V > %LOGFILE% SET SSIS_RSLT=type %LOGFILE% FOR /f "tokens=1 delims=" %%i IN ('%SSIS_RSLT% ^| find /C /I "DTSER_SUCCESS"') DO ( IF %%i EQU 1 ( SET RESULT_CODE=1 SET RESULT_MSG=Package !DTSXFILE! succeeded. See log file !LOGFILE! for details. ) ELSE ( SET RESULT_CODE=-2 SET RESULT_MSG=Package !DTSXFILE! failed. See log file !LOGFILE! for details. )) ECHO %RESULT_CODE% ECHO %RESULT_MSG%
This script can be extended by using RED parameters in place of the paths etc. e.g. create a RED parameter for DTEXECPATH with value "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" and then replace the path in the script with $PDTEXECPATH$.
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article