Powershell workflow and output onto a custom object

Question

I have written the script below, the problem however is that the html report is not generated below the custom object is blank, having written a verison of thesame script without workflows, this works fine. I am wondering if I am missing something here. Secondly, is there any other powershell technology that I can use other than workflows, Ps version is v5. Thanks in advance.

Workflow RunExecution
    { 



Function Check-Instance-Connection{
    param
    (
        [Parameter(Mandatory=$true,
                    ValueFromPipelineByPropertyName=$true,
                    Position=0)]
                    $sql_server,                    

                    [Parameter(Mandatory=$true,
                    ValueFromPipelineByPropertyName=$true,
                    Position=1)]
                    $db_name         
    )

    try
    {
        #Return extra useful info by using custom objects
        $check_outcome = "" | Select-Object -Property log_date, stage, status, error_message             
        $check_outcome.log_date = (Get-Date)
        $check_outcome.stage = 'Ping SQL instance for $sql_server'

        #test connection for a sql instance
        $connectionstring = "Data Source=$sql_server;Integrated Security =true;Initial Catalog=$db_name;Connect Timeout=5;"
        $sqllconnection = New-Object System.Data.SqlClient.SqlConnection $connectionstring
        $sqllconnection.Open();
        $check_outcome.status = $true
        $check_outcome.error_message = ''

        return $check_outcome

    }
    Catch
    {   
        $check_outcome.status = $false
        $check_outcome.error_message = $_.Exception.Message
        return $check_outcome

    }

    finally{
        $sqllconnection.Close();              

    }
}



$file_list = @("deployment_1.sql","deployment_2.sql","deployment_3.sql","deployment_4.sql","deployment_5.sql")

$x = (1,"server1\DEV3",3,1),(4,"serer1\DEV2",6,2),(3,"serer2\DEV1",4,3)
$k = 'serverid','servername','locationid','appid'     
$h = @{}


For($i=0;$i -lt $x[0].length; $i++){
    $x |
        ForEach-Object{
            [array]$h.($k[$i]) += [string]$_[$i]
        }


} 

$folder = "C:\Temp\"
$database_name = "Test"
$all_server_ids = $h['serverid']


    foreach -parallel ($server_id in $all_server_ids)
    {
        $severid = $h["serverid"][$all_server_ids.indexof($server_id)]
        $servername =  $h["servername"][$all_server_ids.indexof($server_id)]
        $locationid =  $h["locationid"][$all_server_ids.indexof($server_id)]

        foreach ($file in $file_list)
        {



            # $check_fine = $is_instance_ok.check_outcome 
             if ($check_fine -eq $true){
                invoke-sqlcmd -ServerInstance "$servername" -inputfile $folder$file  -Database  "$database_name" -Querytimeout 60 -OutputSqlErrors $true -ConnectionTimeout 10 -ErrorAction Continue 
                $report_output += New-Object -TypeName psobject -Property @{Serverid = $severid  ;SQLInstance = $servername ;DeploymentFile = $release_file} 
            }
        }

    }

    $html_report = $report_output | select Serverid,SQLInstance,DeploymentFile | ConvertTo-Html -Property Serverid,SQLInstance,DeploymentFile
    $html_report | Out-File -FilePath 'H:\rpeort\html_report.html'

}

 RunExecution

Answer 1

Hi，

Try putting the lines that aren't valid in workflow (lines 94-95 in your script) in a inlinescript block

InlineScript {  
    $html_report = $using:report_output | select Serverid,SQLInstance,DeploymentFile | ConvertTo-Html -Property Serverid,SQLInstance,DeploymentFile  
    $html_report | Out-File -FilePath 'H:\rpeort\html_report.html'  
}  

Best Regards，
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,
See if this works

$report_output =@()  
foreach -parallel  ($server_id in $all_server_ids)  
{  
    $severid = $h["serverid"][$all_server_ids.indexof($server_id)]  
    $servername =  $h["servername"][$all_server_ids.indexof($server_id)]  
    $locationid =  $h["locationid"][$all_server_ids.indexof($server_id)]      
          
    $report_temp =@()  
    foreach ($file in $file_list)  
    {  
        # $check_fine = $is_instance_ok.check_outcome   
        if ($check_fine -eq $true){  
            invoke-sqlcmd -ServerInstance "$servername" -inputfile $folder$file  -Database  "$database_name" -Querytimeout 60 -OutputSqlErrors $true -ConnectionTimeout 10 -ErrorAction Continue                 
            $report_temp =InlineScript{  
                $temp =@()   
                $temp += $using:report_temp  
                $temp += New-Object -TypeName psobject -Property @{Serverid = $using:severid  ;SQLInstance = $using:servername ;DeploymentFile = $using:release_file}   
                $temp  
            }                
        }              
    }  
    $workflow:report_output += $report_temp                                
}      
InlineScript{  
    $html_report = $using:report_output | select Serverid,SQLInstance,DeploymentFile | ConvertTo-Html -Property Serverid,SQLInstance,DeploymentFile  
    $html_report | Out-File -FilePath 'H:\report\html_report.html'  
}  

Best Regards，
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.