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

ram V 0 Reputation points
2023-10-01T14:09:32.9533333+00:00

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

User's image

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.

User's image

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

User's image

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,486 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,777 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. QuantumCache 20,106 Reputation points
    2023-10-05T23:24:07.6733333+00:00

    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?

    0 comments No comments