Azure Data Storage pipeline to purge SQL table

Question

I am wondering if I can create an Azure pipeline and have it run a proc that purges my Azure SQL table.

I am still new to the concept of data factories and I see that most often than not, the data factories have pipelines to copy over data from either a blob to azure SQL/on premise SQL or the other way around.

I am trying to write a data factory pipeline that will purge old records in my Azure SQL DB and was hoping if anyone can point me in the right direction. Can I still use the Azure data factory for this ?

Answer 1

My suggestion is to use Azure Automation instead of ADF to schedule stored procedure execution. You will find examples here and here. Below is the code you need to implement on Azure Automation:

workflow NAME-OF-YOUR-WORKFLOW
{
    Write-Output "JOB START BEFORE INLINESCRIPT"

    inlinescript
    {
        Write-Output "JOB START"
        # Create connection to Master DB
        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $MasterDatabaseConnection.ConnectionString = "Data Source=YOUR-DATABASE-SERVER-NAME.database.windows.net;Initial Catalog=YOUR-DATABASE-NAME;Integrated Security=False;User ID=YOUR-DATABASE-USERNAME;Password=YOUR-DATABASE-PASSWORD;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
        $MasterDatabaseConnection.Open()

        Write-Output "CONNECTION OPEN"

        # Create command
        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
        $MasterDatabaseCommand.CommandText = "YOUR-PROCEDURE-NAME"

        Write-Output "DATABASE COMMAND TEXT ASSIGNED"

        # Execute the query
        $MasterDatabaseCommand.ExecuteNonQuery()

        Write-Output "EXECUTING QUERY"

        # Close connection to Master DB
        $MasterDatabaseConnection.Close() 

        Write-Output "CONNECTION CLOSED"
    }    
    Write-Output "WORK END - AFTER INLINESCRIPT"
}

To learn more about Azure automation, please click here.

Hope this helps.