Question 170809 in Azure Question posted in
The official documentation to get you started can be found here.

Can Azure run multiple queries as source & insert into Excel file in different sheets?

I have this case where we have different queries we want to run in SQL Server then save it in an Excel file. I parameterized the Excel dataset I have then assigned it in the "Sheet name" property but I noticed when using Copy Data Activity the Sink property is not showing any datasets with Excel only CSV Datasets.

I’ve been looking for work around on this but I’m only seeing parts in the net that reads the Excel file with multiple sheets then iterate those with For Each. But what I need is vice versa. I want to write into a single Excel file the different results of my 3 queries in each different sheets using ADF.

Example:

FileName: Results.xlsx

SELECT * FROM Table1

Insert in Results.xlsv in sheet named Result1.

SELECT * FROM Table2

Insert in Results.xlsv in sheet named Result2.

SELECT * FROM Table3

Insert in Results.xlsv in sheet named Result3.

Tags:

2

Answers


  1. 0

    Unfortunately ADF doesn’t support Excel file as a sink till now.
    For your above use case, you would have to write your own custom logic in Azure functions, Databricks or Azure batch etc.

    Login or Signup to reply.
  2. 0

    As called out by Nandan , Excel is currently not supported as sink in ADF . I see that you have added to sql server tag and so I am assuming that you are using on-premise server , if thats the case you can use a simple powershell script . I have shared a fairly but working powershell script .below

    #Install-Module -Name ImportExcel

$WSName =  @("result1","result2")
$Queries =  @("SELECT GETDATE() AS TimeOfQuery","SELECT GETDATE() AS TimeOfQuery")
foreach ($ws in $WSName)
{
$QueryOp= Invoke-Sqlcmd -Query $Queries[$WSName.IndexOf($ws)] -ServerInstance "Your ServerName"
$QueryOp.TimeOfQuery|Export-Excel "C:tempQueryresult.xlsx" -Append -WorksheetName $ws
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search