CODEWITHSUNDEEP
azureazure-synapseazure-synapse-analyticsazure-synapse-pipeline
Scott Stensland
Scott Stensland

Reputation: 28315

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

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'.,},],'",

Upvotes: 0

Views: 949

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11529

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

enter image description here

Upvotes: 1

Related Questions