Delete data from destination tables before running copy data activity

user_12398336 0
Viewed 12521

I am trying to create a pipeline that moves data between some Oracle databases. I have successfully created and tested the copy data activities. My problem is I want to empty the destination tables before the "copy" runs and I can't see a way to do that (right now it appends data to the tables).

At first I tried using the Stored Procedure activity, but that only supports SQL Server-related sources. None of the other activities offered by data factory work for this use case. Even a generic "execute SQL" activity could work for me, but nothing like that appears to be available.

EDIT: I have posted an answer below, but it's not ideal. Please post an answer if you think you have a better solution!

azure-data-factory azure oracle sql
3 Answers

I use "TRUNCATE TABLE xxx" in pre-copy script. (Truncate is faster than Delete)

Also, to your point, there is no "Execute SQL task", but as a hack, we can use a "Lookup" activity to execute ad-hoc scripts. Link to one of my previous answers on the same : https://stackoverflow.com/a/59422740/2993606

user_29936060

I have found a slightly hacky solution. There is a configuration in the Sink part of the Copy Data activity called Pre-copy script. The documentation says to use it for manipulating the incoming data so I thought it would only work on the incoming data, but it's actually SQL that's executed on the target database (seemingly in a transaction, which is committed)

So basically, this works:

sql in copy data activity

I'm not a huge fan of this solution, because it's hard to configure the flow. I would have preferred a separate activity so I could prepare success/failure options, while with this, the whole "Copy Data" activity will fail. Please post an answer if you have a better idea!

user_123983360

I have always used a Stored Procedure to clear table data as part of my pipelines. I do this because I usually have a specific where clause for the data [that typically lines up with a table partition] rather than truncating the entire table.

As an alternative to the Copy activity, you can use a Data Flow (ADFDF) activity. ADFDF Sink settings show you can truncate the table:

enter image description here

This screenshot is for a SQLDW Sink, so I don't know if the ADFDF Sink for Oracle permits this or not.

user_758380
Related