question

Hi @rajd-9527,

Thanks for using Microsoft Q&A !!
I understand correctly, you want to export your SQL query results from an Azure SQL Database to ADLS Gen2 storage as a Json file.
If that's the case, you can achieve this by using your machine's local drive as an interim storage location to generate the file and then upload it to ADLS Gen2 Storage account. Please find below the updated code to upload query results to ADLS Gen2.

 $InstanceName = "{ServerName}.database.windows.net"
 $connectionString = "Server=$InstanceName;Database={DB Name};Authentication=Sql Password;User={UserName};Password={Password}"
        
 $query = "select * from [dbo].[Test]"
        
 $connection = New-Object System.Data.SqlClient.SqlConnection
 $connection.ConnectionString = $connectionString
        
 $connection.Open()
 $command = $connection.CreateCommand()
 $command.CommandText = $query
        
 $result = $command.ExecuteReader()
        
 $table = new-object "System.Data.DataTable"
        
 $table.Load($result)
     
 $inputFilePath = "c:\input_data.json"
 $table | select $table.Columns.ColumnName | ConvertTo-Json | Out-File $inputFilePath
        
 $connection.Close()
    
 $ctx = New-AzStorageContext -StorageAccountName '{Storage Account Name}' -StorageAccountKey '{Storage Account Key}'
    
 $filesystemName = "input"
 $dirname = "/"
 $destPath = $dirname + (Get-Item $inputFilePath).Name
 New-AzDataLakeGen2Item -Context $ctx -FileSystem $filesystemName -Path $destPath -Source $localSrcFile -Force

Here I am storing query results as a file on system's local drive using Out-File and later connecting to respective Gen2 Storage account using New-AzStorageContext with storage account key and password. Once connection is established, I am using New-AzDataLakeGen2Item to upload the exported json file to ADLS Gen2 storage account.

Please refer to the documentation to get details on using PowerShell cmdlets on Storage account.
Please let me know if you have any questions.

Thanks
Saurabh

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.