How to perform Archiving and purging in Azure SQL database

Question

I'm using Azure SQL database as my source database.Which include 10 tables. I need to do archiving and purging in the table in a period gap of one year.Currently I am using Azure data factory to move data from source database to data warehouse.Can I do archiving and purging using data factory.If not please suggest me a right way to perform this Activity.

Answer 1

Can I do archiving and purging using data factory?

Yes, you can. Azure Data Factory support Azure data warehouse. You can followed this tutorial: Transform data by using the SQL Server stored procedure activity in Azure Data Factory.

Firstly, you need to create a stored procedure in your database.

For example, below is the procedure to delete all the table data in database:

IF EXISTS (SELECT * FROM sysobjects WHERE type='P' and name=N'P_DeleteAllData' ) 
    DROP PROCEDURE P_DeleteAllData 
GO 

CREATE PROCEDURE P_DeleteAllData 
AS
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
    EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'  
    EXEC sp_MSForEachTable 'DELETE FROM ?'  
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
    EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL' 
GO 

Create a stored procedure activity, choose the Azure data warehouse as the linked service.

Choose the stored procedure in you data warehouse:

Add a trigger to schedule the pipeline execute:

Hope this helps.