ADF precopy script to delete before uploading-CodePudding

In ADF copy activity,in precopyscript for sink DB, is it possible to execute the precopy script only based on certain condition ? Please let know if any such option.

CodePudding user response：

You can use "add dynamic content" on the pre-copy script to use the ADF expression language to put logic into the pre-copy. For example use an IF statement.

CodePudding user response：

You can write the expression in Pre-copy script under sink settings in Copy data activity.

This is my existing data in the table. Here I want to delete records with TerritoryID=2 if a condition is matched using copy data activity before loading new data.

Below is the expression to use pipeline variables in the expression and using concat function in the expression to include variables in with the script.

Pre-copy script:

@{concat('BEGIN
IF ( ',variables('value'),' = 10 ) 
  delete from [dbo].[sales]
  where convert(varchar,TerritoryID) = ', variables('ID'),';
END')}

Output: The existing records with TerritoryID = 2 is deleted and new records were inserted.