Truncating tables in Azure Data Factory Pre-Copy script?-CodePudding

I am building a pipeline, and now I need to truncate my destination tables in azure sql db, but before that I need to truncate the destination tables. but I can't figure out the script:

Click to view the ADF screenshot for SINK settings

instead, I put this code but that is wrong because it runs before every copy of the tables (5 times) and truncates all the table except the last one. so I need to make it parameterized I guess:

*truncate table [dbo].[Global_data.csv]
truncate table [dbo].[Option_data.csv]
truncate table [dbo].[State_data.csv]
truncate table [dbo].[Status_data.csv]
truncate table [dbo].[Target_data.csv]*

Also please see my source parameters:

**ADLSv2 container: @pipeline().parameters.SourceContainer
ADLSv2 Directory: @pipeline().parameters.SourceDirectory
ADLSv2 filename:  @item().name
Sink TableName:   @item().name**

So I'm guessing that my pre-script must be something like: truncate table @item().name but this resulted an error for me:

Error Screenshot

DetailsErrorCode= SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near '@item'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near '@item'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near '@item'.,},],'

when I use TRUNCATE TABLE [@{item()}] , I get below error 5 times (one for each table accordingly):

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot find the object "{"name":"StateMetadata.csv","type":"File"}" because it does not exist or you do not have permissions.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Cannot find the object "{"name":"StateMetadata.csv","type":"File"}" because it does not exist or you do not have permissions.,Source=.Net SqlClient Data Provider,SqlErrorNumber=4701,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=4701,State=1,Message=Cannot find the object "{"name":"StateMetadata.csv","type":"File"}" because it does not exist or you do not have permissions.,},],'

CodePudding user response：

Please use truncate table TRUNCATE TABLE [@{item().name}]