sqlazureazure-synapseazure-data-factory

Synapse/ ADF - How to Truncate table if dynamic config column is True in pre-copy script

I want to truncate tables where the value of my column isTrun in the config table is True.

The scripts need to run in the Sink pre-copy script of the copy activity.

The dataflow is the following:

The look-up activity looks up the config table. Then enters the for each loop and copy's the tables that are looked up. before the copy, there needs to be a truncate for the tables that have in the config table the value True in the isTrun column.

The problem that I am facing is that all my tries have invalid syntax.

I already tried the followings scripts:

if(@item().isTrun = 1, TRUNCATE TABLE [@{item().targetSchema}].[@{item().targetTable}], '1=1')

and this script:

TRUNCATE TABLE @{if(equals(item().isTrun, 'True'),'[@{item().targetSchema.item()}].[@{targetTable}]',' ')}

Thanks in advance!

Solution

  • Use this expression for the pre-copy script:

    @{if( 
equals(item().isTrun,1), 
concat( 'truncate table ', item().doel_schema, '.', item().doel_tabel ) ,'')}

    It checks if isTrun equals 1 for this loop, if so it concatenate the truncate command with the table name. If not, the pre-script will be an empty string that does nothing.