locked
Copy Activity Error logging in to ASQL table error on output.errors[0].Message RRS feed

  • Question

  • Hello team,

    I am trying to log copy activity failure error in to Azure SQL using below expression and facing the error.

    Expression:- 

    '@{activity('Copy Activity').output.errors[0].Message}'

    Similar expression works for error code

    '@{activity('Copy Activity').output.errors[0].Code}'

    even tried below expression, but same error

    '@{activity('Copy Activity').output.errors}'

    and Azure SQL column is nvarchar(max), 

    Error:-

    Please advise on the error.


    Thanks


    • Edited by miruan Friday, April 24, 2020 8:38 AM
    Friday, April 24, 2020 8:36 AM

Answers

  • Yes, tried above approach and encountered below error.

    "errorCode": "InvalidTemplate",
        "message": "The expression 'activity('Copy Activity').output.errors.Message' cannot be evaluated because property 'Message' cannot be selected. Array elements can only be selected using an integer index.",
        "failureType": "UserError",


    The first expression in your question is working for me. 

    In copy activity, I have SQL Script SELECT 1/0 to get the error and I set the variable with error message in Set Variable activity. I set the variable data type to string. 


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by miruan Monday, April 27, 2020 7:03 PM
    Monday, April 27, 2020 10:28 AM
  • Finally found a simple solution for the INSERT and able to log full error in SQL table.

    '@{replace(activity('Copy Activity').output.errors[0].Message,'''','')}'
    Thank you Vaibhav.


    Thanks


    • Marked as answer by miruan Monday, April 27, 2020 7:01 PM
    • Edited by miruan Monday, April 27, 2020 7:03 PM
    Monday, April 27, 2020 7:01 PM

All replies

  • Did you try below one?

     '@{activity('Copy Activity').Output.errors.Message



    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Friday, April 24, 2020 6:11 PM
  • Yes, tried above approach and encountered below error.

    "errorCode": "InvalidTemplate",
        "message": "The expression 'activity('Copy Activity').output.errors.Message' cannot be evaluated because property 'Message' cannot be selected. Array elements can only be selected using an integer index.",
        "failureType": "UserError",


    Thanks

    Monday, April 27, 2020 6:33 AM
  • Yes, tried above approach and encountered below error.

    "errorCode": "InvalidTemplate",
        "message": "The expression 'activity('Copy Activity').output.errors.Message' cannot be evaluated because property 'Message' cannot be selected. Array elements can only be selected using an integer index.",
        "failureType": "UserError",


    The first expression in your question is working for me. 

    In copy activity, I have SQL Script SELECT 1/0 to get the error and I set the variable with error message in Set Variable activity. I set the variable data type to string. 


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by miruan Monday, April 27, 2020 7:03 PM
    Monday, April 27, 2020 10:28 AM
  • It's very easy to log errors in SSIS. Go to Event Handlers tab, select OnError from dropdown. Now here you can send email on any error or you can develop your own custom logic to log error into DB or write to text file. Also, you can choose from existing logging mechanism available in SSIS.
    Monday, April 27, 2020 10:49 AM
  • Yes you are right, the first expression worked, able to capture the error, 

    '@{activity('Copy Activity').output.errors[0].Message}'
    but INSERT error message into SQL table, ended up with below error due to Single quotes in the error message. Please help how to overcome unclosed quotation error.


    "Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,
    'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
    Message=A database operation failed with the following error: 
    'Unclosed quotation mark after the character string 'ailure happened on '\n);\nselect 1;
    '.\r\nIncorrect syntax near 'ailure happened on '\n)
    ;\nselect 1;'.',Source=,''Type=System.Data.SqlClient.SqlException,
    Message=Unclosed quotation mark after the character string 'ailure happened on '\n);\nselect 1;'.
    \r\nIncorrect syntax near 'ailure happened on '\n);
    \nselect 1;'


    Thanks


    • Edited by miruan Monday, April 27, 2020 6:31 PM
    Monday, April 27, 2020 6:29 PM
  • Finally found a simple solution for the INSERT and able to log full error in SQL table.

    '@{replace(activity('Copy Activity').output.errors[0].Message,'''','')}'
    Thank you Vaibhav.


    Thanks


    • Marked as answer by miruan Monday, April 27, 2020 7:01 PM
    • Edited by miruan Monday, April 27, 2020 7:03 PM
    Monday, April 27, 2020 7:01 PM