How to drop table if exists in synapse pipeline pre copy script ?

Question

I have a synapse pipeline with a Copy activity doing a "Bulk insert" so need to drop table ... this syntax works ONLY IF TABLE already exists @{concat('drop table ', item().target_schema, '.', item().target_object)} naturally first time I run this pipeline target table does NOT exist so pipeline fails I need to fix above to use functional equivalent to DROP TABLE IF EXISTS
however that syntax fails in azure synapse with error https://stackoverflow.com/questions/48777206/drop-table-if-exists-not-working-on-azure-sql-data-warehouse I have tried many many possible syntax as per

@{concat('IF EXISTS (SELECT [name] FROM sys.tables WHERE [name] like ', item().target_schema, '.', item().target_object,')
 BEGIN
   DROP TABLE ', item().target_schema, '.', item().target_object, ';
 END; '
)}

which errors with

{
    "errorCode": "2200",
    "message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'The multi-part identifier \"myschema.mytable\" could not be bound.',Source=,''Type=System.Data.SqlClient.SqlException,Message=The multi-part identifier \"myschema.mytable\" could not be bound.,Source=.Net SqlClient Data Provider,SqlErrorNumber=4104,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=4104,State=1,Message=The multi-part identifier \"myschema.mytable\" could not be bound.,},],'",
    "failureType": "UserError",
    "target": "Copy data1",
    "details": []
}

also tried so for other SQL this is appropriate

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL 
  DROP TABLE dbo.Scores; 

however my below translation of above into synapse pre copy script syntax errors

@{concat('IF  OBJECT_ID(', item().target_schema, '.', item().target_object, ', \'U\') IS NOT NULL
   DROP TABLE ', item().target_schema, '.', item().target_object, ';'
)}

this also errors out

@{concat('IF  OBJECT_ID(', item().target_schema, '.', item().target_object, ', "U") IS NOT NULL
   DROP TABLE ', item().target_schema, '.', item().target_object, ';'
)}

below also errors out

@{concat("IF  OBJECT_ID(", item().target_schema, ".", item().target_object, ", 'U') IS NOT NULL
   DROP TABLE ", item().target_schema, ".", item().target_object, ";"
)}

Has anyone managed to run a Copy activity pre copy script to drop table IF exists ?

Answer 1

Hello @sekhemrekhutawysobekhotep , Thanks for the question and using MS Q&A platform. When you are changing the TSQL script like this

if object_id ('dw.dim_location','U') is not null drop table dw.dim_location;

Please keep in mind that ' also needs to be accounted for, I am trying to highlight the same in the below

I think the attched expression should help you out . EXPRESSION.TXT There should be NO "new line" character I know at times I struggle with dynamic expression and what I do is I will try and use the SET variable activity and check the expression there. Once I have the expression I will copy and run the same in SSMS and cross check it.

Thanks
Himanshu
Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues.