Automate Azure Synapse Integration Tasks from PowerShell

The CData ADO.NET Provider for Azure Synapse is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Azure Synapse.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Azure Synapse; this tutorial shows how to use the Provider to create, retrieve, update, and delete Azure Synapse data.

Once you have acquired the necessary connection properties, accessing Azure Synapse data in PowerShell can be enabled in three 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.

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

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

Insert Azure Synapse Data

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

Delete Azure Synapse Data

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