Loading data incrementally from Synapse serverless pool External table to Azure SQL by using microsoft standard template pipeline but 'MergeData' script is failing with the error code "2011" and message is "Argument {0} is null or empty.\r\nParameter name

Question

Loading data incrementally from Synapse serverless pool External table to Azure SQL by using microsoft standard template pipeline but 'MergeData' script is failing with the error code "2011" and message is "Argument {0} is null or empty.\r\nParameter name

In Merge Data Activity i have used the following code and it's Successfully executing in Azure Sql . but in pipeline it's failing with following error.

SQL CODE

Declare @TargetTable nvarchar(200) = '@{item().TABLE_NAME}';
Declare @CDCTable nvarchar(200) ='@{item().CDC_TABLE_NAME}'

declare @UpdateColumns nvarchar(max);
declare @Columns nvarchar(max);
Select @UpdateColumns = COALESCE(@UpdateColumns + ', ', '') + 'T.' + x.COLUMN_NAME + ' = S.'  + x.COLUMN_NAME,
  @Columns = COALESCE(@Columns + ', ', '')  + x.COLUMN_NAME 
 from INFORMATION_SCHEMA.COLUMNS x 
WHERE TABLE_NAME =  replace(@TargetTable,'dbo.','')
and  TABLE_SCHEMA = 'dbo'

select @UpdateColumns As UpdateColumns , @Columns As Columns

Declare @MergeStatement nvarchar(max);

set @MergeStatement 
=  ' MERGE ' + @TargetTable + ' T USING '+ @CDCTable + ' S' + 
' ON T.RECID = S.RECID' +
' WHEN MATCHED and S.DML_Action = ''AFTER_UPDATE''' +
'    THEN UPDATE SET ' +
 @UpdateColumns +
' WHEN NOT MATCHED BY TARGET and S.DML_Action <> ''DELETE'' THEN INSERT (' + 
@Columns +
')	Values (' +
@Columns + 
')' +
' WHEN MATCHED and S.DML_Action = ''DELETE''' +
' THEN DELETE;'; 

Execute sp_executesql  @MergeStatement;


update SynapseToSQLTables set Status = 1, LastProcessedFile = '@{item().LAST_UPDATED_CDC_FILE}'
where TableName = @TargetTable;

-- drop table at the end 
drop table @CDCTable

Incremental Pipeline is reference taken from the following link https://github.com/microsoft/Dynamics-365-FastTrack-Implementation-Assets/blob/master/Analytics/SynapseToSQL_ADF/readme.md

Answer 1

Hello @ram V

Please verify that the external table in Synapse serverless pool has data that can be loaded into Azure SQL. You can do this by running a SELECT statement on the external table in Synapse serverless pool to ensure that it returns data?

Check if the parameters used in the MergeData script are correct and not null or empty. The error message suggests that one of the parameters is null or empty. You may need to modify the script to handle these cases.

I assume external table and the target table have the same schema? right?