Search code examples
azureazure-sql-databaseexport-to-csv

Automate CSV file generation from Azure SQL database

I am new to Azure. I have a automated process that populates data into a table on Azure SQL Database. Now, I am looking for an automated way of exporting the data out of this table in a CSV format to an On-premises location. (From there the file will be sent to a vendor) By Automation I mean a scheduled process which can run every couple of hours.

How can this be achieved?

Solution

  • There are many ways can auto export the Azure SQL database table data as a csv file to an on-premise location.

    The best way we suggest you is using Data Factory, when the pipeline created, you can create a trigger and schedule execute the pipeline.

    Reference:

    1. Copy and transform data in Azure SQL Database by using Azure Data Factory
    2. Copy data to or from a file system by using Azure Data Factory
    3. Pipeline execution and triggers in Azure Data Factory

    You also could use bellow ways:

    1. You can also use SSIS to implement an automated task.You can simply just copy data between databases (cloud -> On prem) with a scheduled SSIS package Export to CSV.
    2. You of course can use BCP but it will be cumbersome in the long run. A lot of scripts, tables, maintenance. No logging, no metrics, no alerts... Don't do it honestly.

    Ref: Azure SQL DB - data file export (.csv) from azure sql

    Hope this helps.