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 ?
