none
How to expect error msg when SSIS fails RRS feed

  • Question

  • I m new to SSIS 2012. I m using below method in SQL agent job step to run an SSIS pkg, but the same do not return error/failure when SSIS pkg errors and stops.  Basically, I want job step to fail and throw proper error msg of SSIS error.

    Declare @execution_id bigint
    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'EmailPaystoreETL.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'PayrollReportETL', @project_name=N'PayrollETL', @use32bitruntime=False, @reference_id=Null
    Select @execution_id
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED',  @parameter_value=1; -- true
    EXEC [SSISDB].[catalog].[start_execution] @execution_id
    GO

     Thanks


    Mahesh


    Thursday, October 20, 2016 1:45 AM

Answers

  • Hi Mahesh,

    The view [SSISDB].[catalog].[operations] includes the execution information, check the status column and try below code.

     
    Declare @execution_id bigint
    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'some folder', @project_name=N'Integration Services Project1', @use32bitruntime=False, @reference_id=Null
    Select @execution_id
      
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED',  @parameter_value=1; -- true
    EXEC [SSISDB].[catalog].[start_execution] @execution_id
     
    --
    IF NOT EXISTS( 
    SELECT *
      FROM [SSISDB].[catalog].[operations] where operation_id=@execution_id and status=7 --success
      )
      BEGIN
       THROW 60000, 'JOB FAILED', 1;  
      END 



    Eric Zhang
    TechNet Community Support

    • Marked as answer by Mahesh Dasari Thursday, October 20, 2016 1:46 PM
    Thursday, October 20, 2016 5:42 AM
    Moderator

All replies

  • Hi Mahesh,

    you can use error log for SSIS packages which mention in following link.

    CREATE TABLE [dbo].[ErrorLog]
    ( [ErrorID] [int] IDENTITY(1,1) NOT NULL, 
    [ErrorRow] [xml] NULL, 
    [ErrorMessage] [varchar](1000) NULL, 
    [ErrorIssuingPackage] [varchar](50) NULL, 
    [ErrorSourceTable] [varchar](50) NULL, 
    [ErrorDestinationTable] [varchar](50) NULL, 
    [ErrorLoggingTime] [datetime] NULL )


    https://www.mssqltips.com/sqlservertip/2149/capturing-and-logging-data-load-errors-for-an-ssis-package/


    Please click Mark As Answer if my post helped.

    Thursday, October 20, 2016 4:34 AM
  • Hi Mahesh,

    The view [SSISDB].[catalog].[operations] includes the execution information, check the status column and try below code.

     
    Declare @execution_id bigint
    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'some folder', @project_name=N'Integration Services Project1', @use32bitruntime=False, @reference_id=Null
    Select @execution_id
      
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED',  @parameter_value=1; -- true
    EXEC [SSISDB].[catalog].[start_execution] @execution_id
     
    --
    IF NOT EXISTS( 
    SELECT *
      FROM [SSISDB].[catalog].[operations] where operation_id=@execution_id and status=7 --success
      )
      BEGIN
       THROW 60000, 'JOB FAILED', 1;  
      END 



    Eric Zhang
    TechNet Community Support

    • Marked as answer by Mahesh Dasari Thursday, October 20, 2016 1:46 PM
    Thursday, October 20, 2016 5:42 AM
    Moderator
  • What the status of your job after completing with error FAILED or SUCCESS.

    If Failed You can set alert to be out when failed.

    If success even on package got failed, then you need to look into SSIS package design to generate error and break the execution instead of skipping error and continue. 


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, October 20, 2016 8:52 AM
  • Tím Mitchell explains very well how to solve this issue, have a look at this:

    https://www.timmitchell.net/post/2016/11/28/a-better-way-to-execute-ssis-packages-with-t-sql/



    • Edited by NicolasFontan Thursday, October 18, 2018 11:30 AM Hyperlink missing
    Thursday, October 18, 2018 11:19 AM