0

I would like to delete the MySQL DB records using ADF.

I have created the pipeline in ADF and I am copying the data from a MySQL Database to Storage Account using copy activity in ADF, once that's completed, I would like to delete those copied records from MySQL database.

I am not able to find out any activity which can allow to delete the records from MySQL Database.

Script Activity doesn't allow MySQL linked Service only SQL DB allowed.

Please need your suggestion to complete it.

Ghansham Mahajan
  • 31
  • 3

2 Answers2

1

You can use lookup activity which supports both SQL and MySQL with query after copy activity to delete the records after copy.

enter image description here

After the copy activity join it with the look up and give your source dataset.

Select query and give the truncate query to delete the records in the table.

truncate table [dbo].[output];

enter image description here

I have added the select script above only to avoid the lookup error which gives error if the query didn’t give any data. However, it will truncate the records in the table even after giving error.

If you want to delete the total table, you can give drop query.

drop table <tablename>;

Data copied to blob storage after copy activity:

enter image description here

Table after copy activity:

enter image description here

Here I did it using azure SQL database. You can do the same with Azure MySQL Database as lookup supports both.

Rakesh Govindula
  • 5,257
  • 1
  • 2
  • 11
0

You need to create a stored procedure in your Database and add the stored procedure activity as a final step in your Azure Data Factory pipeline. If you'd like to truncate the whole data once the copy is finished, here's how you would create your Stored Procedure:

GO
CREATE PROCEDURE SP_Truncate
AS
BEGIN
    TRUNCATE TABLE mytable
END

Once you've created this, add a stored procedure activity as a last step in your Azure Data Factory. It'll delete the copied data. Read a bit more about this in the documentation; you can also add parameters in your stored procedure, which you can refer to using lookup activity. Let me know if you need more help.

Moein
  • 101
  • 5