Search code examples
triggersazure-sql-databasetableau-apiazure-data-factory

Copy data from on-prem SQL server DB to Azure SQL DB. Then truncate the tables in Azure SQL DB after data is copy to Tableau CRM

I want to automate below tasks/scenario with the help of trigger or function. As of now performed these steps through Azure Data Factory by manually running the pipeline.

  1. Copy data from on-prem sql server to Azure SQL DB automatically whenever new data is populated in on-prem sql db.

  2. Truncate tables from Azure SQL DB whenever a pull request comes from Tableau CRM via Microsoft Azure SQL DB connector.

Don't want to include azure synapse analytics into picture.

Need you kind suggestion on this.

Solution

  • You can leverage Copy Data tool to Ingest data on schedule.

    enter image description here

    enter image description here

    Select your on-premise database as source

    enter image description here

    Note: You would have to use self-hosted IR for source linked service (on-prem sql db). Refer: Tutorial: How to access on-premises SQL Server from Data Factory Managed VNet using Private Endpoint

    Select new rows only

    enter image description here

    Next, you can choose an existing table or create a new one with source table schema

    enter image description here

    Make customer requirements changes

    enter image description here

    Finally, validate and deploy!

    And for truncating tables after copy...

    There is a provision for Stored Procedure and Pre-copy scripts in CopyActivty though but you might have to use explicit StoredProcedure Activity after the CopyActvity to clear the tables

    enter image description here

    enter image description here

    OR.. Better

    enter image description here