I am copying data from a SQL table to another one. The sink has a stored procedure which in which a 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:
