I am creating a workflow in Azure data factory and I wanted to create a excel file with data from the SQL table (Azure SQL server) in any one below scenario:
There is no out of the box feature in ADF to support Excel as a sink. You would have to use logic app or write your own custom logic via Azure function, Databricks, Azure batch etc to do the above tasks
Write a SQL stored procedure Python in SQL Server Machine learning Services (will only work if SQL Server Machine Learning Services is configured). ADF can then run the stored procedure and place the file into the blob storage. The Blob storage Python libraries are not available on SQL Managed Instance, so the script below uses the Blob REST API's instead.
EXECUTE sp_execute_external_script @language = N'Python'
,@input_data_1 = N'SELECT A.* FROM MyTable A'
,@input_data_1_name = N'SQLQueryOutput'
,@script = N'
import pyodbc
import pandas as pd
import requests
# Execute the query to retrieve the data from the SQL Server table
df = SQLQueryOutput
# Write the data to an Excel file
df.to_excel(FileName, index=False)
url = "https://" + AccountName + ".blob.core.windows.net/" + BlobContainerPath + "/" + FileName + "?" + BlobSASToken
payload= open(FileName, "rb")
headers = {
''x-ms-blob-type'': ''BlockBlob'',
''Content-Type'': ''application/vnd.openxmlformats-officedocument.spreadsheetml.sheet''
}
response = requests.request("PUT", url, headers=headers, data=payload)
print(response.text)
'
,@params = N'@BlobSASToken VARCHAR(200), @AccountName VARCHAR(100), @BlobContainerPath VARCHAR(200), @FileName VARCHAR(100)'
,@BlobSASToken = N'sp=xxxxxxxxxxxxx&st=xxxxxxxxxxxx&se=xxxxxxxxxxxx&spr=https&sv=xxxxxxxxxxxxxxx&sr=c&sig=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
,@AccountName = N'myaccountname'
,@BlobContainerPath = N'containername/containerpath'
,@FileName = N'myexcelfilename.xlsx'
