Public/Update-SqlConnections.ps1
|
function Update-SqlConnections { <# .SYNOPSIS Updates SQL connection strings in TMDL files within a Fabric semantic model. .DESCRIPTION The Update-SqlConnections function processes TMDL files in a Fabric semantic model, replacing old datawarehouse URLs and lakehouse names with new values in SQL connection strings. This is particularly useful when migrating Fabric semantic models between environments or when datawarehouse or lakehouse names have changed. .PARAMETER FolderPath The path to the folder containing the Fabric semantic model (PBISM files). The function will process TMDL files in the "definition/tables" subfolder. .PARAMETER OldDatawarehouseUrl The original datawarehouse URL to be replaced in connection strings. .PARAMETER NewDatawarehouseUrl The new datawarehouse URL to replace the old one. .PARAMETER OldLakehouseName The original lakehouse name to be replaced in connection strings. .PARAMETER NewLakehouseName The new lakehouse name to replace the old one. .PARAMETER Files Optional. Array of specific file names to process. If omitted, all TMDL files will be processed. File extensions (.tmdl) will be appended automatically if not provided. .PARAMETER ErrorOnMissingFiles Optional. When specified, the function will throw an error if any files specified in the Files parameter cannot be found. .PARAMETER WhatIf Optional. When specified, the function will report what files would be modified without actually making any changes. .EXAMPLE Update-SqlConnections -FolderPath "C:\Models\SalesModel" ` -OldDatawarehouseUrl "workspace://dw.fabric.microsoft.com/dev" ` -NewDatawarehouseUrl "workspace://dw.fabric.microsoft.com/prod" ` -OldLakehouseName "dev-lakehouse" ` -NewLakehouseName "prod-lakehouse" Updates all TMDL files in the SalesModel, replacing the dev datawarehouse URL and lakehouse name with prod values. .EXAMPLE Update-SqlConnections -FolderPath "C:\Models\SalesModel" ` -OldDatawarehouseUrl "workspace://dw.fabric.microsoft.com/dev" ` -NewDatawarehouseUrl "workspace://dw.fabric.microsoft.com/prod" ` -OldLakehouseName "dev-lakehouse" ` -NewLakehouseName "prod-lakehouse" ` -Files "Sales", "Product" ` -WhatIf Shows what changes would be made to only the Sales.tmdl and Product.tmdl files without actually changing them. .NOTES The function specifically targets string replacements in TMDL files, which contain connection information for Fabric semantic models. It ignores DateTableTemplate files and processes only valid TMDL files. When replacing lakehouse names, the function is careful to only replace names that appear within quotation marks to avoid unintended replacements. #> [CmdletBinding()] param( [Parameter(Mandatory=$true)] [string]$FolderPath, [Parameter(Mandatory=$true)] [string]$OldDatawarehouseUrl, [Parameter(Mandatory=$true)] [string]$NewDatawarehouseUrl, [Parameter(Mandatory=$true)] [string]$OldLakehouseName, [Parameter(Mandatory=$true)] [string]$NewLakehouseName, [Parameter(Mandatory=$false)] [string[]]$Files = @("*"), [Parameter(Mandatory=$false)] [switch]$ErrorOnMissingFiles, [Parameter(Mandatory=$false)] [switch]$WhatIf ) $itemsInFolder = Get-ChildItem -LiteralPath $FolderPath -Depth 1 | Where-Object { @(".pbism") -contains $_.Extension } if ($itemsInFolder.Count -eq 0) { Write-Error "Cannot find valid item definitions ( *.pbism) in the '$FolderPath'" return } $FolderPath = Join-Path -Path $FolderPath -ChildPath "definition/tables" # Append .tmdl to each file name in $Files if it's not "*" and doesn't already end with .tmdl if ($Files -and $Files -ne @("*")) { $Files = $Files | ForEach-Object { if ($_ -ne "*" -and -not ($_ -like "*.tmdl")) { "$($_).tmdl" } else { $_ } } } # Find all TMDL files in the specified folder and subfolders $allFiles = Get-ChildItem -Path $FolderPath -Filter "*.tmdl" -Recurse | Where-Object { ($_.Name -notmatch "^(DateTableTemplate_|LocalDateTable_)") } # Filter files based on the Files parameter if ($Files -and $Files -ne @("*")) { $foundFiles = $allFiles | Where-Object { $Files -contains $_.Name } # Check if all specified files were found if ($ErrorOnMissingFiles -and $foundFiles.Count -lt $Files.Count) { $foundFileNames = $foundFiles | ForEach-Object { $_.Name } $missingFiles = $Files | Where-Object { $_ -ne "*" -and $foundFileNames -notcontains $_ } if ($missingFiles) { Write-Error "The following files were not found: $($missingFiles -join ', ')" return } } if (-not $foundFiles) { Write-Warning "No matching files found with the specified names" return } $files = $foundFiles } else { $files = $allFiles } Write-Host "Found $($files.Count) files to process" -ForegroundColor Cyan foreach ($file in $files) { Write-Host $file $content = Get-Content -Path $file -Raw # Check if the file contains the strings we're looking for if ($content -match [regex]::Escape($OldDatawarehouseUrl) -or $content -match [regex]::Escape($OldLakehouseName)) { Write-Host "Processing file: $($file)" -ForegroundColor Yellow # Replace the datawarehouse URL $updatedContent = $content -replace [regex]::Escape($OldDatawarehouseUrl), $NewDatawarehouseUrl # Replace the lakehouse name - being careful to only replace it in connection strings $pattern = "(`"|')$([regex]::Escape($OldLakehouseName))(`"|')" $replacement = "`$1$NewLakehouseName`$2" $updatedContent = $updatedContent -replace $pattern, $replacement # Check if content was modified if ($content -ne $updatedContent) { if ($WhatIf) { Write-Host " Would update: $($file)" -ForegroundColor Cyan } else { Set-Content -Path $file -Value $updatedContent -NoNewline Write-Host " Updated: $($file)" -ForegroundColor Green } } } } } |