CODEWITHSUNDEEP
azureazure-sql-databaseexport-to-csv
Mumuksh
Mumuksh

Reputation: 3

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?

Upvotes: 0

Views: 3339

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 13009

There are good amount of ways to do this:

  1. Schedule a Azure Data Factory pipeline
  2. Schedule an SSIS package using azure data factory or using sql server agent job
  3. Have a powershell script, which is scheduled using task scheduler

I am giving powershell script below, which I generally use to quickly get data from a specific table to CSV file. You can schedule this powershell script using task scheduler or using Execute-Process task in SSIS package.

Invoke-sqlcmd -ConnectionString "AzureSQLDBConnectionString"  `
-Query "SET NOCOUNT ON;SELECT * FROM TableName" -MaxCharLength 700 `
-QueryTimeout 1200 | Export-Csv -NoTypeInformation -path C:\temp\Tablename.csv -Encoding UTF8

Upvotes: 0

Leon Yue
Leon Yue

Reputation: 16431

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.

Upvotes: 1

Related Questions