Model Context Protocol (MCP) finally gives AI models a way to access the business data needed to make them really useful at work. CData MCP Servers have the depth and performance to make sure AI has access to all of the answers.
Try them now for free →
Automate Azure Synapse Integration Tasks from PowerShell
Are you in search of a quick and easy way to access Azure Synapse data from PowerShell? This article demonstrates how to utilize the Azure Synapse Cmdlets for tasks like connecting to Azure Synapse data, automating operations, downloading data, and more.
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.
-
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") -
Connect to Azure Synapse:
$conn= New-Object System.Data.CData.AzureSynapse.AzureSynapseConnection("User=myuser;Password=mypassword;Server=localhost;Database=Northwind;") $conn.Open() -
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()
CodeProject
Ready to get started?
Download a free trial of the Azure Synapse Data Provider to get started:
Download NowLearn more:
Azure Synapse ADO.NET Provider
Rapidly create and deploy powerful .NET applications that integrate with Azure Synapse.
