none
Azure Data Factory Merging operation failed

    Question

  • HI All,

    I am using Azure Data Factory Copy Activity to Merge the data from source to target table and using following stored procedure to merge the data and it's not working. Could some one help me to understand the issue here.

    1) I created the table type in target table having same schema as source.

    /****** Object:  UserDefinedTableType [dbo].[Employee_Type]    Script Date: 5/17/2018 10:37:36 AM ******/
    CREATE TYPE [Employees_Type] AS TABLE(
    [ID] [int] NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Amount] int NULL,
    [ODS_SourceVersion] [bigint] NOT NULL,
    [ODS_SourceOperation] [nchar](1) NOT NULL,
    [ODS_IsDeleted] [bit] NOT NULL
    )
    GO

    2) Then using following stored procedure to merge the data.

    /****** Object:  StoredProcedure [dbo].[sp_UpSert_Employees]    Script Date: 5/17/2018 10:20:29 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[sp_UpSert_Employees] @Employees Employees_Type READONLY
    AS

    MERGE dbo.Employees AS TARGET
    USING @Employees AS SOURCE
    ON (
    TARGET.[ID] = SOURCE.[ID]
    )
    WHEN MATCHED THEN
    UPDATE SET
    TARGET.[ID] = SOURCE.[ID]
    ,TARGET.[FirstName] = SOURCE.[FirstName]
    ,TARGET.[LastName] = SOURCE.[LastName]
    ,TARGET.[Amount] = SOURCE.[Amount]
    ,TARGET.[ODS_SourceVersion] = SOURCE.[ODS_SourceVersion]
    ,TARGET.[ODS_SourceOperation]= SOURCE.[ODS_SourceOperation]
    ,TARGET.[ODS_IsDeleted] = SOURCE.[ODS_IsDeleted]
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (
          [ID] 
    ,[FirstName] 
    ,[LastName] 
    ,[Amount]
    ,[ODS_SourceVersion]
    ,[ODS_SourceOperation]
    ,[ODS_IsDeleted]
    )
    VALUES (
      SOURCE.[ID] 
    ,SOURCE.[FirstName] 
    ,SOURCE.[LastName] 
    ,SOURCE.[Amount]
    ,SOURCE.[ODS_SourceVersion]
    ,SOURCE.[ODS_SourceOperation]
    ,SOURCE.[ODS_IsDeleted]
    );

    but it is failing with following error here.

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

    Thursday, May 17, 2018 3:46 PM

All replies

  • Good question?  Does ADF v2 support READONLY parameters yet?

    How are you populating your TVP in the pipeline?

    If ADF v2 doesn't yet support READONLY parameters then maybe your only solution is to use a custom .Net component and pass the dataset in ADO.Net.

    Friday, May 18, 2018 8:16 AM
  • Hi,

    I had a similar error. Eventually I noticed that both my input and output datasets tables were prefixed with the schema name. I removed the schema (dbo) from the table name and that fixed the error.

    i.e.

    {
        "name": "Output set",
        "properties": {
            "published": false,
            "type": "AzureSqlTable",
            "linkedServiceName": "myService",
            "typeProperties": {
                "tableName": "[dbo].[myTable]"   ---> change to [myTable]
            },
            "availability": {
                "frequency": "Month",
                "interval": 1
            }
        }
    }

    Hope that helps,

    David


    • Edited by Dicedpeppa Sunday, July 1, 2018 8:35 PM
    • Proposed as answer by Dicedpeppa Sunday, July 1, 2018 8:36 PM
    Sunday, July 1, 2018 8:34 PM
  • Hi, 

    When using TVP to insert data into SQL DB, the table name of the target ADF dataset has to be the same value as the parameter name of the TVP in your SP definition.

    In this case, the TVP parameter name in your SP is "Employee". So the table name of the target dataset also has to be "Employee", which means following dataset definition:

    {
      "name":
      "AzureSQLDbDataset",
      "properties":
      {
        "type": "AzureSqlTable",
        "linkedServiceName":
        {
          "referenceName": "<linked service name>",
          "type": "LinkedServiceReference"
        }, 
        "typeProperties":
        {
          "tableName": "Employee"
        }
      }
    }





    Monday, July 2, 2018 6:00 AM