Running an arbitrary Integration Services package from a Host Script

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$.