Use Data factorys to archive SQL data to json files on a blob storage

Question

Hey,

I am completely new to data factorys but though that I can use them to fulfill my archiving requirements.
I have a small azure sql schema with only 5-6 tables in it. Those tables contain a lot of data though and that data needs to be archived on a scheduled basis. Archived means, delete rows which are older then 12 month (there is a column that can be used for the check) from the table and put those rows in a json file stored on a blob storage.
Looking into data factories I can:

• set up the azure sql as a source dataset
• create a filter to filter the source table based on the requirement "older then 12 month"
• create a sink that fill push the filtered rows to a json

What is unclear to me is the deleting part. The rows I pushed to the json should be deleted from the DB. But how? I added an "alter" step with "delete if" but the table is not touched at all.

Can someone help?

Answer 1

Hi @Pascal Jahr ,

Thank you for posting query in Microsoft Q&A Platform.

You can use Copy activity to copy rows older than 12 months to Json file in blob storage and then use script activity to perform delete rows operation on SQL table.

Below are the detailed Implementation details.
Step 1: Copy activity, under Source tab use dataset pointing to your SQL Table. Select Query option there and write query that gives you rows older than 12 months.

Step 2: Script Activity, write SQL query to delete older rows from table in it. Please check this video to know more about script activity.

Hope this helps. Please let me know if any further queries.

-----------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification