CODEWITHSUNDEEP
sql-serverssis
Anil
Anil

Reputation: 1857

How to catch error occurred in SSIS

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?

Upvotes: 0

Views: 1175

Answers (2)

Alexander Volok
Alexander Volok

Reputation: 5940

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'

Upvotes: 0

under
under

Reputation: 3077

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

Upvotes: 1

Related Questions