question

RohitKulkarni-6062 avatar image
0 Votes"
RohitKulkarni-6062 asked AvireniUpendar-3214 commented

Powershell script running in Runbook

Hello Team, I am running the PowerShell script in runbook to extract the data from DB and copy in csv format in local system. Whether I am doing the right thing not sure. While running the script in run book i got an error .Please refer the screenshot : ![75656-image.png][1] ![75702-image.png][2] [1]: /answers/storage/attachments/75656-image.png [2]: /answers/storage/attachments/75702-image.png Thanks RK

azure-synapse-analyticsazure-automation
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello anonymous user,

Thanks for the ask and using the Microsoft Q&A platform.

Could you please share the PowerShell script as a code sample to test from our end?

0 Votes 0 ·

$SQLServer = "servername.sql.azuresynapse.net"
$SQLDBName = "demanddev"
$uid ="xyz"
$pwd = "***"

SQL Query

$SqlQuery = "SELECT * from Table1;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

Creating Dataset

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | out-file "C:\File\test.csv"

0 Votes 0 ·

Hello anonymous user,

Thanks for sharing the code snippet. I will try to repro from my end and get back to you.

Stay tuned!

0 Votes 0 ·
RohitKulkarni-3496 avatar image
0 Votes"
RohitKulkarni-3496 answered PRADEEPCHEEKATLA-MSFT commented

Pradeep : I am not able to see the Accept Answer option.

Please consider as Accept Answer

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @RohitKulkarni-3496,

Accept Answer is only visible to the Original Poster.

77719-image.png

Note: This question was asked by anonymous user, you need to sign-in with this profile (anonymous user ) to see Accept Answer Button.

77698-image.png


  • Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.


0 Votes 0 ·
image.png (143.5 KiB)
image.png (120.0 KiB)

Hello @RohitKulkarni-3496,

Just checking to see if you got chance to sign-in with this profile (anonymous user )and click on Accept Answer Button.

0 Votes 0 ·
RohitKulkarni-3496 avatar image
0 Votes"
RohitKulkarni-3496 answered AvireniUpendar-3214 commented

I got clear now.

I ran the below script in local powershell script.The file got saved in local drive.But data is appearing in rows wise.It has to be column wise.Where i need to apply delimiter.So that data can appear in column wise

I am running the below script in the Azure Automation runbook .I am able to see the Number of records in the table in Azure auotmation runbook

$SQLServer = "workspace-dev-ondemand.sql.azuresynapse.net"
$SQLDBName = "workpace"
$uid ="Sqladminuser"
$pwd = ""

SQL Query

$SqlQuery = "SELECT * from Table1;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = false; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

Creating Dataset

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | out-file "C:\File\test.csv"

Azure automation powershell:

76765-image.png



But my requirement is store the csv file in local/server system drive.




Please advise.

Regards
RK


image.png (57.1 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello anonymous user,

As I said on second point " Unfortunately, you cannot pass local directory path in the Azure Automation runbook as $DataSet.Tables[0] | out-file "C:\File\test.csv".".

End of the powershell script, you need to add upload it to Blob storage using Set-AzureStorageBlobContent. See https://savilltech.com/2018/03/25/writing-to-files-with-azure-automation/ for an example. And then download from Azure Blob Storage.

For more details, refer Azure Automation, RunBooks and T-SQL Query output directly to Azure Blob Storage


0 Votes 0 ·

Hello anonymous user,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hi Team,

I want to export SQL Server 2016 query output into Azure blob storage account. Part of this I need to establish connection between our company SQL Server 2016 (LOWSSQLWD971) in an Azure Powershell runbook.

Could anyone suggest on how can I achieve this?

0 Votes 0 ·
PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT edited

Hello anonymous user,

Couple of important points to note while running the powershell scripts in Azure Automation runbook.

Before running your powershell script in Azure Automation runbook, test it in local PowerShell mode and figure out any issues.

When running the above code in local powershell mode, it got the below error message: Exception calling "Fill" with "1" argument(s): "Windows logins are not supported in this version of SQL Server."

76652-image.png

Change Integrated security to false in the connection string and pass username and password which will work without any issue.

76614-image.png

Checkout the file saved in local filesystem path:

76586-image.png

You may checkout the SO thread which addressing similar issue.

Unfortunately, you cannot pass local directory path in the Azure Automation runbook as $DataSet.Tables[0] | out-file "C:\File\test.csv".

Reason: If you need to create temporary files as part of your runbook logic, you can use the Temp folder (that is, $env:TEMP) in the Azure sandbox for runbooks running in Azure. The only limitation is you cannot use more than 1 GB of disk space, which is the quota for each sandbox. When working with PowerShell workflows, this scenario can cause a problem because PowerShell workflows use checkpoints and the script could be retried in a different sandbox.

The best option for a location to create temporary files is under the $env:TEMP location. Note that any data is lost as soon as the runbook execution has completed however it makes a good storage location for temporary files that are hopefully stored longterm in something like Azure Storage and the temporary location is to download the data for access.

 $SQLServer = "cheprasynapse.sql.azuresynapse.net"
 $SQLDBName = "chepra"
 $uid ="XXXXXXXXXX"
 $pwd = "XXXXXXXXXXXXX"
 $SqlQuery = "SELECT * from Trip"
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = false; User ID = $uid; Password = $pwd;"
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
 $SqlCmd.CommandText = $SqlQuery
 $SqlCmd.Connection = $SqlConnection
 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SqlAdapter.SelectCommand = $SqlCmd
 $DataSet = New-Object System.Data.DataSet
 $SqlAdapter.Fill($DataSet)
 $DataSet.Tables[0] | out-file ("$Env:temp"+"\test.csv")

The easiest option is to continue writing the file as you are now, then after the file is written have your Powershell code upload it to Blob storage using Set-AzureStorageBlobContent. See https://savilltech.com/2018/03/25/writing-to-files-with-azure-automation/ for an example.

For more details, refer Azure Automation, RunBooks and T-SQL Query output directly to Azure Blob Storage

Hope this helps. Do let us know if you any further queries.


Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.



image.png (59.5 KiB)
image.png (48.5 KiB)
image.png (34.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Pradeep,

I got confused with your answer In one of the screen you shared you are able to save the csv file in local system.
And next point you are saying you are not able to save the file in local system.

Which one need to consider not able to find.
Please advise.

Regards
RK

0 Votes 0 ·

Hello anonymous user,

To make it clear to you:

First point: Before running your powershell script in Azure Automation runbook, test it in local PowerShell mode and figure out any issues.

I'm running on Local PowerShell on my computer.

Second Point: Unfortunately, you cannot pass local directory path in the Azure Automation runbook as $DataSet.Tables[0] | out-file "C:\File\test.csv".

If you are using Azure Automation, you need to use $env:TEMP location or Azure Storage account.

Hope this helps. Do let us know if you any further queries.

0 Votes 0 ·