I'm running a SSIS package which is developed in Visual Studio 2019 using dtexec command given below. We are using SQL Server Management Studio 2019 version. Packages are deployed to Integration Services Catalogs.
DTEXEC /ISSERVER "\SSISDB\Folder\Project\Package.dtsx" /SERVER xxx /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CHECKPOINTING OFF /REPORTING EW
If there is an error in the package, all I see is this:
Microsoft (R) SQL Server Execute Package Utility
Version 15.0.2000.5 for 32-bit
Copyright (C) 2019 Microsoft. All rights reserved.
Started: 1:56:31 PM
Package execution on IS Server failed. Execution ID: 457, Execution Status:4.
To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report
Started: 1:56:31 PM
Finished: 1:56:45 PM
Elapsed: 13.703 seconds
To check the error, I should go to All Executions on Integration Services Catalog.
If I run the Dtexec command from skybot job, how do I get the error log if skybot job fails? Ideally, I'd like to get error, error code, source and description details in the following format:
Started: 9:00:12 PM
Error: 2021-01-11 21:00:13.15
Code: 0x00000008
Source: SQL_Task
Description: The script returned a failure result.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:00:12 PM
Finished: 9:00:13 PM
Elapsed: 0.733 seconds