Friday, January 6, 2012

SSIS: Calling an SSIS package from a batch file

I typically execute my SSIS packages on an app server using the dtexec utility and a batch file.  This gives several advantages:
  1. It allows me to execute the package from an automated scheduler program (we use autosys)
  2. Allows for a config file in a different location than when the package was created
  3. Outputs a log file in a specified location with the date in the file name
Here is an example batch file that  I use:
@echo off

FOR /F "tokens=2-4 delims=/ " %%i IN ('date /t') DO SET DATE=%%i-%%j-%%k
dtexec /FILE E:\SSIS_PACKAGES\PSFeedSSIS\PSFeedSSIS.dtsx /DECRYPT password /ConfigFile E:\SSIS_PACKAGES\PSFeedSSIS\PSStageConfig.dtsConfig /CHECKPOINTING OFF > E:\SSIS_PACKAGES\PSFeedSSIS\Logs\%DATE%PSStageLog.txt
if ERRORLEVEL 0 SET ERRORLEV=0
if ERRORLEVEL 1 SET ERRORLEV=1
echo ERRORLEVEL = %ERRORLEV%
exit /B %ERRORLEV%
echo PS Stage Complete

Some pieces of the batch file:
  • the /FILE parameter gives the name of the package itself
  • /DECRYPT gives the package password if set
  • /ConfigFile gives the location of the config file
  • /CHECKPOINTING OFF  checkpointing will not be used during execution
  • the redirect > puts the output log file to the specified location.  The logfile name will have the date in the filename (%DATE%) variable
  • The last part of the batch file is setting the Error level then propagating that error code out to the exit code of the batch file.  This was necessary in our case so that the autosys scheduler program would recognize a failure condition.
Click Here for more information on the dtexec utility.

1 comment:

Achilles33 said...

Informative...will try this code.