locked
Copy Activity Error RRS feed

  • Question

  • Hello!
    We have been looking at this error which we got back from calling a Stored Proc with @TVP Parameters using a Copy Activity. None of us can figure out what it is really saying because there is no DailyID Column as either a TVP parameter nor DailyID column in the DB Table. Can you please help us interpret this?

    Thanks

    Mike

    "errorCode": "2200", "message": "ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'DailyID' contains an invalid value '2019-03-22T10:00:00'.,Source=Microsoft.DataTransfer.ServiceLibrary,''Type=System.ArgumentException,Message=Type of value has a mismatch with column typeCouldn't store <2019-03-22T10:00:00> in DailyID Column. Expected type is Byte[].,Source=System.Data,''Type=System.ArgumentException,Message=Type of value has a mismatch with column type,Source=System.Data,'", "failureType": "UserError", "target": "Copy Hourly Gas" }


    Mike Kiser

    Tuesday, July 2, 2019 8:47 PM

Answers

  • We restricted the column based on the JSONPathDefinition property set by customers.  In this case, you are getting the auto-detected columns from the first JObject.  Please set the JsonPathDefinition with only these data columns that you need then have another try. thanks.
    Wednesday, July 3, 2019 5:07 AM
  • I think I might have found the reason for the "@" in the area above. I am setting a Table Type for TVP parameter for a Stored Proc call to @variables('TableType') (see pic below). Will this cause the variable to have a @ in front of it when it is sent to the DB? 

    I am not sure when to put a @ in front of things and when not to....

    Also I just saw this in the Copy Activity Code: Does this mean that the @ will be sent as part of the stored proc name (which it should NOT be)?

    "sink": {
    "type": "SqlSink",
    "writeBatchSize": 10000,
    "sqlWriterStoredProcedureName": {
    "value": "@variables('FinalStoredProcName')",
    "type": "Expression"
    },
    "sqlWriterTableType": {
    "value": "@variables('TableType')",
    "type": "Expression"
    }

    Thanks again!
    Mike


    Mike Kiser


    • Edited by EMKISER Wednesday, July 3, 2019 4:30 PM
    • Marked as answer by EMKISER Wednesday, July 10, 2019 11:35 PM
    Wednesday, July 3, 2019 4:05 PM

All replies

  • We restricted the column based on the JSONPathDefinition property set by customers.  In this case, you are getting the auto-detected columns from the first JObject.  Please set the JsonPathDefinition with only these data columns that you need then have another try. thanks.
    Wednesday, July 3, 2019 5:07 AM
  • Thanks wenbof,

    That helped alot. Once I changed the type to "make it" map...I now get the following

    I have searched everywhere in my pipeline looking for a "@" but there is none, and none in the mapping.....any idea where this error might be coming from. Since the "@" is not in the JsonPathDefinition, I have no idea where ADF is picking up the "@". Any ideas would be very appreciated.

    Thanks
    MIke

    Error
    { "errorCode": "2200", "message": "ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'The procedure \"usp_HourlyGas_TvpMerge\" has no parameter named \"@\".',Source=,''Type=System.Data.SqlClient.SqlException,Message=The procedure \"usp_HourlyGas_TvpMerge\" has no parameter named \"@\".,Source=.Net SqlClient Data Provider,SqlErrorNumber=349,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=349,State=1,Message=The procedure \"usp_HourlyGas_TvpMerge\" has no parameter named \"@\".,},],'", "failureType": "UserError", "target": "Copy Hourly Gas" }


    Mike Kiser

    Wednesday, July 3, 2019 3:35 PM
  • I think I might have found the reason for the "@" in the area above. I am setting a Table Type for TVP parameter for a Stored Proc call to @variables('TableType') (see pic below). Will this cause the variable to have a @ in front of it when it is sent to the DB? 

    I am not sure when to put a @ in front of things and when not to....

    Also I just saw this in the Copy Activity Code: Does this mean that the @ will be sent as part of the stored proc name (which it should NOT be)?

    "sink": {
    "type": "SqlSink",
    "writeBatchSize": 10000,
    "sqlWriterStoredProcedureName": {
    "value": "@variables('FinalStoredProcName')",
    "type": "Expression"
    },
    "sqlWriterTableType": {
    "value": "@variables('TableType')",
    "type": "Expression"
    }

    Thanks again!
    Mike


    Mike Kiser


    • Edited by EMKISER Wednesday, July 3, 2019 4:30 PM
    • Marked as answer by EMKISER Wednesday, July 10, 2019 11:35 PM
    Wednesday, July 3, 2019 4:05 PM