Powershell workflow and output onto a custom object

MrFlinstone 501 Reputation points
2020-12-21T15:17:13.353+00:00

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
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,431 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 32,156 Reputation points Microsoft Vendor
    2020-12-22T09:17:44.49+00:00

    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.


  2. Ian Xue (Shanghai Wicresoft Co., Ltd.) 32,156 Reputation points Microsoft Vendor
    2020-12-25T07:32:38.59+00:00

    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.

    0 comments No comments