Truncate table Sink Pre-copy script

Question

So I am copying 10 different tables from Azure SQLDB to Azure SQLMI, I need to truncate the destination table before it inserts the data. But I am just cannot get to it work. BTW total n00b at Azure ADF.

I have tried a couple of different solutions in other threads but they just do not work for me.

Tried TRUNCATE TABLE @item().destination.table TRUNCATE TABLE [@{item().name}]

"Operation on target ForEach_Loop failed: Activity failed because an inner activity failed; Inner activity name: Copy_Tables, Error: The expression 'item().name' cannot be evaluated because property 'name' doesn't exist, available properties are 'source, destination'."

Help please?

Answer 1

"Operation on target ForEach_Loop failed: Activity failed because an inner activity failed; Inner activity name: Copy_Tables, Error: The expression 'item().name' cannot be evaluated because property 'name' doesn't exist, available properties are 'source, destination'."

The error indicates that there are only source and destination keys for every item and the tablename must be inside the destination object.

And as per your dynamic content, your ForEach input array can be like this.

[ 
{ 
    "source": 
        { 
        "table": "Source1"
         }, 
     "destination": 
         { 
         "table": "Target1" 
         } 
 }, 
 {
     "source": 
         { 
         "table": "Source2" 
         }, 
     "destination": 
         { 
         "table": "Target2" 
         } 
 }, 
 {
     "source": 
         { 
         "table": "Source3" 
         }, 
     "destination": 
         { 
         "table": "Target3" 
         } 
 }
 ]

So, give the Truncate query in the pre-copy script using string interpolation Truncate table @{item().destination.table} as suggested by @Nandan in comments.

This is my sample Example using a copy activity.

You can see the script here: