How to read SQL rows as PowerShell parameters-CodePudding

On Azure, I am running multiple .sql files from a container in 100s of Azure SQL Databases via Powershell runbook.

I want Powershell to read the server name and the database name to run the scripts from my SQL Server table that looks like this:

Servername	Databasename	Status
Server-01	DB-01	Process
Server-01	DB-02	Skip
Server-02	DB-03	Process

In my current version of the Powershell script, it can read the files in the container and run them in a given server and database:

# Get the blob container
$blobs = Get-AzStorageContainer -Name $containerName  -Context $ctx | Get-AzStorageBlob 

# Download the blob content to localhost and execute each one 
foreach ($blob in $blobs)
    {
        $file = Get-AzStorageBlobContent -Container $containerName -Blob $blob.Name  -Destination "." -Context $ctx 
        Write-Output ("Processing file :"   $file.Name)
        $query = Get-Content -Path $file.Name
        Invoke-Sqlcmd -ServerInstance "Server-01.database.windows.net" -Database "DB-01" -Query $query -AccessToken $access_token
        Write-Output ("This file is executed :"   $file.Name) 
    }

I am looking for a method that will read the rows from the table and feed them into the -ServerInstance and -Database fields in the Invoke-Sqlcmd. Ideally it can filter out the Skip rows.

CodePudding user response：

One method is to load the database list into a DataTable and iterate over the list for each query. Change the $databaseListConnectionString in the example code below per your authentication method and set the connection AccessToken if/as needed.

# get database list
$databaseListConnectionString = "Data Source=YourServer;Initial Catalog=YourDatabase"
$databaseListQuery = "SELECT ServerName, DatabaseName FROM dbo.DatabaseList WHERE Status = 'Process';"
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($databaseListQuery, $databaseListConnectionString)
$dataAdapter.SelectCommand.Connection.AccessToken = $access_token
$databaseList = New-Object System.Data.DataTable
[void]$dataAdapter.Fill($databaseList)

# Get the blob container
$blobs = Get-AzStorageContainer -Name $containerName  -Context $ctx | Get-AzStorageBlob 

# Download the blob content to localhost and execute each one 
foreach ($blob in $blobs) {
    {
        $file = Get-AzStorageBlobContent -Container $containerName -Blob $blob.Name  -Destination "." -Context $ctx 
        Write-Output ("Processing file :"   $file.Name)
        $query = Get-Content -Path $file.Name
        foreach($database in $databaseList.Rows) {
            Invoke-Sqlcmd -ServerInstance "$($database.ServerName)" -Database "$($database.DatabaseName)" -Query $query -AccessToken $access_token
            Write-Output ("This file is executed :"   $file.Name)
        }
    }
}