Execute multiple SQL scripts in Azure SQL Database using AzureSQLDACPACDEPLOYMENT task

Question

Hello,

my backend developers has different stored procedures created every time. I need to execute all the stored procedures (SQL Scripts) at once. Earlier, I have used:

task: SqlAzureDacpacDeployment@1

However, this is only working for single SQL Script. If I have 10 SQL Scripts then I have to manually use 10  SqlAzureDacpacDeployment@1 tasks. Doing this will not make my pipeline automated as I have to manually update the tasks every time when there is a new SQL Script added

I am looking for something that can run all the SQL Scripts in a folder in Loop using AuthenticationType: aadAuthenticationPassword

I have tried using the following powershell script but it is giving me errors

            - task: PowerShell@2
              displayName: 'Execute SQL Script usng SQL Azure DACPAC deployment task'
              inputs:
                targetType: 'inline'
                script: |
                  $azureSubscription = $env:azureSubscription
                  $serverName = $env:ServerName
                  $databaseName = $env:servers_mcapshelp_name
                  $scriptDirectory = "$(System.DefaultWorkingDirectory)/sql/SQL Database/MCAPSHelpDB/sqlScripts"
                  $Password = $env:aadSqlPassword
                  $Username = $env:aadSqlUsername
                  
                  
                  # Get all SQL script files in the specified directory
                  $sqlScripts = Get-ChildItem -Path $scriptDirectory -Filter *.sql
                  
                  # Loop through each script and deploy using SQL Azure DACPAC deployment task
                  foreach ($sqlScript in $sqlScripts) {
                      Write-Host "Deploying $($sqlScript.Name)..."
                      
                      # Build SQLAzureDacpacDeployment command
                      $arguments = @{
                          'azureSubscription' = $azureSubscription
                          'authenticationType' = 'aadAuthenticationPassword'
                          'serverName' = $serverName
                          'databaseName' = $databaseName
                          'aadSqlUsername' = $Username
                          'aadSqlPassword' = $Password
                          'deployType' = 'sqlTask'
                          'sqlFile' = $sqlScript.FullName
                      }

                      Write-Host 
                      
                      # Execute SQLAzureDacpacDeployment task
                      Write-Host "Executing SQL Azure DACPAC Deployment Task..."
                      $sqlPackageDeploymentTask = az sql db execute --subscription $azureSubscription --name $databaseName --server $serverName --aadUser $aadSqlUsername --aadPassword $aadSqlPassword --type SqlTask --script-path $sqlScript.FullName

                  
                      # Check the result
                      if ($sqlPackageDeploymentTask.ExitCode -eq 0) {
                          Write-Host "Deployment of $($sqlScript.Name) completed successfully."
                      } else {
                          Write-Host "Deployment of $($sqlScript.Name) failed with exit code $($sqlPackageDeploymentTask.ExitCode)."
                      }
                  }

I need a script where I can execute multiple SQL Files using AAD Authentication. Please help me