How to catch error occurred in SSIS

Question

I am calling a SSIS package inside procedure using xm_cmdshell. Below is the part of code from stored procedure.

DECLARE @ReturnCode INT

EXEC @ReturnCode=xp_cmdshell @cmd

IF @ReturnCode <>0
BEGIN

END

@cmd has the DTEXEC command to execute SSIS package. If SSIS package fails I want to access SSIS error message inside IF clause. How can I achieve this?

Possible duplicate of [Get Results from XP\_CMDSHELL](https://stackoverflow.com/questions/9501192/get-results-from-xp-cmdshell) — Nick.Mc, Mar 28 '18 at 00:12

under · Accepted Answer · 2018-03-27T23:11:51.160

1

To achieve what you want, use SSIS logging from within an SSIS package. For example you can log to a table. In your SQL script, you can read that table after calling xp_cmdshell to get errors.

Note also that MS is moving away from DTExec, look into SSIS catalog

edited Mar 27 '18 at 23:11

answered Mar 27 '18 at 22:48

under

2,519
1
21
40

I am getting 0 in @ReturnCode variable if SSIS run successfully. If SSIS fails value is coming as 1. – Anil Mar 27 '18 at 22:56
Wasn't working for me, although I used it with BCP... I will edit the answer. SSIS catalog and SSIS logging is the way to go though. – under Mar 27 '18 at 23:11

score 0 · Answer 2 · answered Mar 28 '18 at 13:21

Partially, you can achieve this following way:

DECLARE @output TABLE (lines varchar(2000))
DECLARE @ReturnCode INT

INSERT INTO @output
EXEC @ReturnCode=xp_cmdshell @cmd

IF @ReturnCode <>0
BEGIN
--do something with output
SELECT lines FROM @output WHERE lines IS NOT NULL   

END

However, as @under mentioned, consider to use SSIS Catalog. in that case accomplishment of your task could be much simpler:

Start of SSIS package:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name=N'Deployed Projects',
      @project_name=N'Integration Services Project1',
    @use32bitruntime=False,
      @reference_id=Null
Select @execution_id
EXEC [SSISDB].[catalog].[start_execution] @execution_id

Querying for errors:

SELECT * FROM SSISDB.catalog.event_messages
WHERE operation_id = @execution_id AND event_name = 'OnError'

How to catch error occurred in SSIS

2 Answers2