Control-M Execute SSIS package via batch file, capture ssis Error in calling batch file output

0

We have lots of SSIS packages that run via .bat(batch) files from Control-M. The issue we have is that if the SSIS package fails, the batch file ( executed via Control-M Job) never displays the the real SSIS error message, the production support team wants to see these errors within the batch file output logs, which gets displayed within control-m, instead of going separately to SSIS Error reporting.

so for example Batch file error output is (Current output)

Package execution on IS Server failed. Execution ID: 118475, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report

but the real error in SSIS is (Expected output to be appended to current output)

ExtractConform_Master:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Login failed for user 'UK\blah'.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Cannot open database "Blah" requested by the login. The login failed.".

Current Sample batch file looks like this "

echo off

set PackageName=Publish.dtsx
set EnvironmentName=BlahEnvironment
set BasePath=%~dp0..

REM Parameters
REM ====================================================

set Cluster=Clustername
set Subcluster=SubclusterName
set QueueName=QueueName



"%DTExecFilePath%" /ISSERVER     "\%SSISCatalog%\%FolderName%\%ProjectName%\%PackageName%" /SERVER %SSISSERVER% /Envreference %env% /Par "$ServerOption::SYNCHRONIZED(Boolean)";True /Par "Cluster";%Cluster% /Par "Subcluster";%Subcluster% /Par "$Package::QueueName";%QueueName%

echo ERROR LEVEL is %ERRORLEVEL%

IF %ERRORLEVEL% GEQ 1 EXIT /b 1
IF %ERRORLEVEL% EQU 0 EXIT /b 0 code here

Any Advice ?

batch-file
error-handling
ssis
control-m
asked on Stack Overflow Apr 5, 2018 by nlulla

1 Answer

0

I suggest you use the Control-M for Database module to run DTSX from SSIS SQL Server. This will allow you to use a Database Job Type where you can enable the option to append the DTSX execution output.

answered on Stack Overflow Oct 25, 2018 by Raciel H.

User contributions licensed under CC BY-SA 3.0