Azure-docs: Sink Stored Procedure table name is required

[candidson]

Hi all,

I am copying data from a sql table to another one. The sink has a stored procedure which in which an SQL Merge is done. The stored procedure has been tested directly and i can confirm that it works. I also have several different copy-activities with the same logic.

However, for 2 of them, I keep getting the following Factory Validation Output error.

Sink Stored Procedure table name is required

Any idea where this might come from?

Here the type and procedure:

-- create type
create type [sta].[my_type] as table (
    [column1] [nvarchar](255) null,
    [column2] [nvarchar](255) null,
    [column3] [nvarchar](255) null,
    [column4] [nvarchar](255) null,
    [column5] [nvarchar](255) null,
    [column6] [nvarchar](255) null
)

GO

-- create procedure
create procedure [cdw].[sp_load_table] @mytable [sta].[my_type] readonly
as
begin
    merge [cdw].[mytable] as target
    -- handle duplicates
    using (select distinct * from @mytable) as source
    on (
            target.[column1]=source.[column1]
        and target.[column2]=source.[column2]
        and target.[column3]=source.[column3]
        and target.[column4]=source.[column4]
        and target.[column5]=source.[column5]
        and target.[column6]=source.[column6]
    )
    when matched and (
           COALESCE(target.[column1],1)<>COALESCE(source.[column1],1)
        or COALESCE(target.[column2],1)<>COALESCE(source.[column2],1)
        or COALESCE(target.[column3],1)<>COALESCE(source.[column3],1)
        or COALESCE(target.[column4],1)<>COALESCE(source.[column4],1)
        or COALESCE(target.[column5],1)<>COALESCE(source.[column5],1)
        or COALESCE(target.[column6],1)<>COALESCE(source.[column6],1)
    ) then
        update set 
             target.[column1]=source.[column1]
            ,target.[column2]=source.[column2]
            ,target.[column3]=source.[column3]
            ,target.[column4]=source.[column4]
            ,target.[column5]=source.[column5]
            ,target.[column6]=source.[column6]
    when not matched by target
        then insert
            (
             [column1]
            ,[column2]
            ,[column3]
            ,[column4]
            ,[column5]
            ,[column6]

            )
        values
            (
                 source.[column1]
                ,source.[column2]
                ,source.[column3]
                ,source.[column4]
                ,source.[column5]
                ,source.[column6]
            )
    when not matched by source
        then delete;     
end

And here the ADF activity:

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: a2e7af17-5daf-eebf-92b4-433eca93d328
• Version Independent ID: cce235ca-d882-4f70-cacc-b3c2c7feae13
• Content: Copy data to and from SQL Server by using Azure Data Factory
• Content Source: articles/data-factory/connector-sql-server.md
• Service: data-factory
• GitHub Login: @linda33wj
• Microsoft Alias: jingwang

[AdamS-MSFT]

@candidson Thank you for the valuable feedback,we are investigating the issue.

[Mike-Ubezzi-MSFT]

@candidson There appears to be a table missing to allow the stored procedure to function correctly: _Azure Data Factory – using a Stored Procedure as a destination_ (link).
Additionally, can you please identify a document that you are using as a basis for this functionality, as this channel is intended for document feedback. I did find this document: _Copy data to and from SQL Server by using Azure Data Factory_ (link), and will use this as a basis for assigning ownership of the issue.

[candidson]

@Mike-Ubezzi-MSFT Thanks for your feedback. Actually, I do have 6 activities exactly built the same way. They all work except for one.

I did follow the different links you sent - originally...

The stored procedure works fine when tested directly on azure sql db.

Furthermore, the ADF execution doesn't fail... It doesn't even pass validation

[Mike-Ubezzi-MSFT]

@candidson Thank you for the additional feedback, I am assigning to the ADF team for further investigation.

[Mike-Ubezzi-MSFT]

@candidson Additionally, have you been successful with a merge operation in ADF before?

[candidson]

@Mike-Ubezzi-MSFT yes i have. As i was saying, i have several activities built exactly the same way (all with merge tasks). And only two aren't working.

[rasciodc]

Having exactly the same issue here! The scenario described by @candidson reflects exactly my environment and the message I'm getting in the validation is the same.

I have around 18 other pipelines configured like this and they all work fine....

[rafael-yure]

This is in fact a bug. I'm also having this problem. All my pipelines worked perfectly. Now, I assign a procedure, and it doesn't matter if the sink has a dynamic or static table assigned, this still happens.

I have over 30 pipelines working with the same configuration, but now when I try to create new ones (or change the sink dataset of those already created) this message appears.

I've noticed that this is happening with databases that have more than 1 schema.

My older datasets, created when my database didn't have secondary schemas, work. But the new ones, where the Sink configuration asks me to specify a schema, aren't working.

This is critical: I can't deploy my factory to production because of this.

[rafael-yure]

Having exactly the same issue here! The scenario described by @candidson reflects exactly my environment and the message I'm getting in the validation is the same.

I have around 18 other pipelines configured like this and they all work fine....

Did you add another schema on our database? I have a secondary one for logging and temporary data. I'm considering dropping it, create the dataset again just to retry. This is screwing up my deadlines here. Like, it's not like data factory is cheap!

[rafael-yure]

@candidson There appears to be a table missing to allow the stored procedure to function correctly: _Azure Data Factory – using a Stored Procedure as a destination_ (link).
Additionally, can you please identify a document that you are using as a basis for this functionality, as this channel is intended for document feedback. I did find this document: _Copy data to and from SQL Server by using Azure Data Factory_ (link), and will use this as a basis for assigning ownership of the issue.

Mike, this document is for ADFv1. I'm using ADFv2, and like I said, I have 30 pipelines working.

[rasciodc]

Having exactly the same issue here! The scenario described by @candidson reflects exactly my environment and the message I'm getting in the validation is the same.
I have around 18 other pipelines configured like this and they all work fine....

Did you add another schema on our database? I have a secondary one for logging and temporary data. I'm considering dropping it, create the dataset again just to retry. This is screwing up my deadlines here. Like, it's not like data factory is cheap!

Indeed I did add a new Schema today. I had more than one before, but I created a new one just before trying to set this new pipeline that is not coming through.

Actually the same scenario is giving a different error now. in the "Mapping" tab, if I try to "Import Schemas" it will throw an error:

_"Failed to import sink schema. 'Type=System.Data.SqlClient.SqlException,Message=Column, parameter, or variable #1: Cannot find data type #TypeTableName#. Must declare the table variable "@a". Parameter or variable '@a' has an invalid data type"_

It is really messing up my deadlines too.... how great!

[candidson]

Having exactly the same issue here! The scenario described by @candidson reflects exactly my environment and the message I'm getting in the validation is the same.
I have around 18 other pipelines configured like this and they all work fine....

Did you add another schema on our database? I have a secondary one for logging and temporary data. I'm considering dropping it, create the dataset again just to retry. This is screwing up my deadlines here. Like, it's not like data factory is cheap!

Indeed I did add a new Schema today. I had more than one before, but I created a new one just before trying to set this new pipeline that is not coming through.

Actually the same scenario is giving a different error now. in the "Mapping" tab, if I try to "Import Schemas" it will throw an error:

_"Failed to import sink schema. 'Type=System.Data.SqlClient.SqlException,Message=Column, parameter, or variable #1: Cannot find data type #TypeTableName#. Must declare the table variable "@A". Parameter or variable '@A' has an invalid data type"_

It is really messing up my deadlines too.... how great!

For me, activities which were working fine, started acting out and do not work anymore.. throwing out the validation error

[pranavmody]

Facing the exact same validation issue mentioned in this thread. Please help @himanshusinha-msft

[Syncret]

We just identified this is a bug of ADF UI. It's caused by a design change of ADF which splits tableName into schema and table, leading the validation failed to pass. Currently we are working on the fix, to work around it, you can append &feature.splitTableName=false to the end of the ADF web's url to temporarily disable this feature and create a new sql dataset. Or you can also click the code button on the error dataset, and manually add a fake tableName properties like "tableName": "fake", beside the schema and table property to bypass the validation.

[candidson]

We just identified this is a bug of ADF UI. It's caused by a design change of ADF which splits tableName into schema and table, leading the validation failed to pass. Currently we are working on the fix, to work around it, you can append &feature.splitTableName=false to the end of the ADF web's url to temporarily disable this feature and create a new sql dataset. Or you can also click the code button on the error dataset, and manually add a fake tableName properties like "tableName": "fake", beside the schema and table property to bypass the validation.

The workaround with the URL parameter works for me! Thanks! Please let us know once the problem is fixed. thanks!

[Syncret]

We just identified this is a bug of ADF UI. It's caused by a design change of ADF which splits tableName into schema and table, leading the validation failed to pass. Currently we are working on the fix, to work around it, you can append &feature.splitTableName=false to the end of the ADF web's url to temporarily disable this feature and create a new sql dataset. Or you can also click the code button on the error dataset, and manually add a fake tableName properties like "tableName": "fake", beside the schema and table property to bypass the validation.

The workaround with the URL parameter works for me! Thanks! Please let us know once the problem is fixed. thanks!

The fix has been deployed. Refresh the webpage and the issue should be gone. Thanks.

[candidson]

The fix has been deployed. Refresh the webpage and the issue should be gone. Thanks.

Awesome! Thanks a lot!

[rasciodc]

We just identified this is a bug of ADF UI. It's caused by a design change of ADF which splits tableName into schema and table, leading the validation failed to pass. Currently we are working on the fix, to work around it, you can append &feature.splitTableName=false to the end of the ADF web's url to temporarily disable this feature and create a new sql dataset. Or you can also click the code button on the error dataset, and manually add a fake tableName properties like "tableName": "fake", beside the schema and table property to bypass the validation.

The workaround with the URL parameter works for me! Thanks! Please let us know once the problem is fixed. thanks!

The fix has been deployed. Refresh the webpage and the issue should be gone. Thanks.

All good here now! Thanks!

[candidson]

Thanks guys for the fast fix... Closing this thread now