how to drop table if exists in pre-copy script azure synapse pipeline Copy activity

Question

I have an azure synapse pipeline which runs fine if target tables already exist ... Copy activity pre-copy script is

@{concat('drop table ', item().target_schema, '.', item().target_object)}

however I need to edit above syntax to first check if table exists then only drop table if it exists ... below is legal sql server syntax

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[myschema].[mytable]') AND type in (N'U')) DROP TABLE [myschema].[mytable] GO

As you can see my pre-copy script is parameterized since my azure synapse analytics pipeline is doing these Activities :

Lookup -> ForEach -> Copy

so the pre-copy script syntax must also be parameterized

How do I implement the IF EXISTS logic yet put that into the parameterized pre-copy script syntax ?

The following guesses for the pre-copy script ... all error out

if object_id (item().target_schema, '.', item().target_object,'U') is not null drop table item().target_schema, '.', item().target_object

below fails

DROP TABLE IF EXISTS @{item().target_schema}.@{item().target_object}

with error

"message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Parse error at line: 1, column: 12: Incorrect syntax near 'IF'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Parse error at line: 1, column: 12: Incorrect syntax near 'IF'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=103010,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=103010,State=1,Message=Parse error at line: 1, column: 12: Incorrect syntax near 'IF'.,},],'",

score 1 · Accepted Answer · answered Apr 14 '23 at 06:35

This is my lookup output array which I have given to ForEach.

[
        {
            "table_name": "one",
            "schema_name": "dbo"
        },
        {
            "table_name": "two",
            "schema_name": "dbo"
        }
]

I reproduced the above scenario with DROP TABLE IF EXISTS @{item().schema_name}.@{item().table_name} command in pre-copy script of copy activity and got same error.

enter image description here

I tried the same command in synapse SQL database and ended up with same error.

enter image description here

As per this Documentation, the reason for this error might be the above command only applies to SQL Server and not SQL DW.

The following guesses for the pre-copy script ... all error out

if object_id (item().target_schema, '.', item().target_object,'U') is not null drop table item().target_schema, '.', item().target_object

I am able to drop the table in pre-copy script by using string interpolation with above command like below.

if object_id ('@{item().schema_name}.@{item().table_name}','U') is not null drop table @{item().schema_name}.@{item().table_name};

enter image description here

how to drop table if exists in pre-copy script azure synapse pipeline Copy activity

1 Answers1