Read the blog
 

Automate Azure Synapse Integration Tasks from PowerShell

Ready to get started?

Download a free trial:

Download Now

Learn more:

Azure Synapse ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with Azure Synapse.

In this article

Related articles


Are you looking for a quick and easy way to access Azure Synapse data from PowerShell? We show how to use the Cmdlets for Azure Synapse and the CData ADO.NET Provider for Azure Synapse to connect to Azure Synapse data and synchronize, automate, download, and more.

The CData Cmdlets for Azure Synapse are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Azure Synapse.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Azure Synapse API, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Azure Synapse data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Azure Synapse. To access Azure Synapse data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Azure Synapse.

After obtaining the needed connection properties, accessing Azure Synapse data in PowerShell consists of three basic steps.

Connecting to Azure Synapse

In addition to providing authentication (see below), set the following properties to connect to a Azure Synapse database:

  • Server: The server running Azure. You can find this by logging into the Azure portal and navigating to Azure Synapse Analytics -> Select your database -> Overview -> Server name.
  • Database: The name of the database, as seen in the Azure portal on the Azure Synapse Analytics page.

Authenticating to Azure Synapse

Connect to Azure Synapse using the following properties:

  • User: The username provided for authentication with Azure.
  • Password: The password associated with the authenticating user.

PowerShell

  1. Install the module:

    Install-Module AzureSynapseCmdlets

  2. Connect:

    $azuresynapse = Connect-AzureSynapse -User "$User" -Password "$Password" -Server "$Server" -Database "$Database"

  3. Search for and retrieve data:

    $productname = "Konbu" $products = Select-AzureSynapse -Connection $azuresynapse -Table "Products" -Where "ProductName = `'$ProductName`'" $products

    You can also use the Invoke-AzureSynapse cmdlet to execute SQL commands:

    $products = Invoke-AzureSynapse -Connection $azuresynapse -Query 'SELECT * FROM Products WHERE ProductName = @ProductName' -Params @{'@ProductName'='Konbu'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Azure Synapse\lib\System.Data.CData.AzureSynapse.dll")

  2. Connect to Azure Synapse:

    $conn= New-Object System.Data.CData.AzureSynapse.AzureSynapseConnection("User=myuser;Password=mypassword;Server=localhost;Database=Northwind;") $conn.Open()

  3. Instantiate the AzureSynapseDataAdapter, execute an SQL query, and output the results:

    $sql="SELECT Id, ProductName from Products" $da= New-Object System.Data.CData.AzureSynapse.AzureSynapseDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.id $_.productname }

Update Azure Synapse Data

PowerShell

Update-AzureSynapse -Connection $AzureSynapse -Columns @('Id','ProductName') -Values @('MyId', 'MyProductName') -Table Products -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.AzureSynapse.AzureSynapseCommand("UPDATE Products SET ProductName='Konbu' WHERE Id = @myId", $conn) $cmd.Parameters.Add(new System.Data.CData.AzureSynapse.AzureSynapseParameter("@myId","10456255-0015501366")) $cmd.ExecuteNonQuery()

Insert Azure Synapse Data

PowerShell

Add-AzureSynapse -Connection $AzureSynapse -Table Products -Columns @("Id", "ProductName") -Values @("MyId", "MyProductName")

ADO.NET

$cmd = New-Object System.Data.CData.AzureSynapse.AzureSynapseCommand("INSERT INTO Products (ProductName) VALUES (@myProductName)", $conn) $cmd.Parameters.Add(new System.Data.CData.AzureSynapse.AzureSynapseParameter("@myProductName","Konbu")) $cmd.ExecuteNonQuery()

Delete Azure Synapse Data

PowerShell

Remove-AzureSynapse -Connection $AzureSynapse -Table "Products" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.AzureSynapse.AzureSynapseCommand("DELETE FROM Products WHERE Id=@myId", $conn) $cmd.Parameters.Add(new System.Data.CData.AzureSynapse.AzureSynapseParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()