Connecting Synapse Data Warehouse of Microsoft Fabric to Azure DevOps CI/CD Release Pipeline

Question

I have a Microsoft Fabric warehouse, and we have several scripts in Azure DevOps that we aim to execute through a CI/CD release pipeline. I'm seeking guidance on establishing a connection between our release pipeline in Azure DevOps and Microsoft Fabric. Additionally, here is the PowerShell script we use to run the Fabric scripts:

# Define authentication details for Azure Synapse SQL Data Warehouse
$userName = " xx"
$password = "yy"

# Define the Azure Synapse SQL Data Warehouse server name
$connectionString = " V7PULMP2UPRULHPJ3OYUMXKS7Y-    ZDY7HCDTEL2EJLFT4SSIWSHTVA.datawarehouse.pbidedicated.windows.net" 
$databaseName = "MSF warehouse"

# Loop through all files in the specified directory
$sqlFiles = Get-ChildItem -Path "$(System.DefaultWorkingDirectory)\_copy directory\build      artifact\ELT\scripts"

foreach ($file in $sqlFiles) {
if ($file.Extension -eq ".sql") {
Write-Host ("Executing SQL file: " + $file.Name)
$sqlFilePath = $file.FullName

# Run the SQL script using SQLCMD (or use Invoke-Sqlcmd if available)
sqlcmd -S $connectionString -d $databaseName -U $userName -P $password -i $sqlFilePath
}
}

I also tried using the service principal and the personal access token, yet with no success.

Answer 1

I also tried using the service principal and the personal access token, yet with no success.

Were there any error message when using service principal?

From looking at the doc CI CD in Azure Synapse Analytics , i suggest you can create an Azure Resource Manager service connection for the resource group to connect Synapse Data Warehouse.

You can check steps below.

1. create an Azure Resource Manager service connection for the resource group.

   https://learn.microsoft.com/en-us/azure/devops/pipelines/library/service-endpoints?view=azure-devops&tabs=yaml&preserve-view=true

2. Give our Azure DevOps Service Principal access to our Azure Synapse Workspace.

   https://techcommunity.microsoft.com/t5/data-architecture-blog/ci-cd-in-azure-synapse-analytics-part-4-the-release-pipeline/ba-p/2034434

3. Add an "Azure PowerShell" task and select the service connection you created in step 1 then write the script you want to run and see if this works for you.