Question 103206 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Synapse/ ADF – How to Truncate table if dynamic config colomn 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!

Tags:

2

Answers


  1. 0
    • Instead of trying to use string interpolation with if condition, you can directly use if function only. The following is the output of my lookup:

    enter image description here

    • The following is how you can do it. In the pre-copy script that you can use to achieve the requirement.
    @{if(equals(item().isTrun,'True'),concat('TRUNCATE TABLE ',item().table),'')}

    enter image description here

    • So, when the isTrun value is True, then if condition checks and executes the truncate statement and ignores if the isTrun value is false. The following is table t2 (1 row in source and sink) after the above execution (isTrun is false).

    enter image description here

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search