CODEWITHSUNDEEP
powershellreporting-servicesssrs-2008-r2reportingservices-2005start-job
Suraj
Suraj

Reputation: 487

SSRS report using PowerShell Start-Job

I have a PowerShell GUI that is pulling some values from a SSRS report using an String array input. However, as this would freeze the GUI, I decided to use Start-Job to start a job that pulls the SSRS report while a ProgressBar keeps running in the GUI.

The SSRS report has only one input parameter. When I use Start-Job to Render the report using multiple values, I get the result of only the first record irrespective of the number of input values.

The same function works smoothly when called natively without Start-Job returning the records for all the input values.

This is the code:

$GetSSRSData = {
    param([string[]]$InputArray)

    $reportServerURI = "https://<SERVER>/ReportServer/ReportExecution2005.asmx?wsdl"
    $RS = New-WebServiceProxy -Class 'RS' -NameSpace 'RS' -Uri $reportServerURI -UseDefaultCredential
    $RS.Url = $reportServerURI

    $deviceInfo = "<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>"
    $extension = ""
    $mimeType = ""
    $encoding = ""
    $warnings = $null
    $streamIDs = $null

    $reportPath = "/Folder/Report"
    $Report = $RS.GetType().GetMethod("LoadReport").Invoke($RS, @($reportPath, $null))

    # Report parameters are handled by creating an array of ParameterValue objects.
    $parameters = @()

    for($i = 0; $i -lt $InputArray.Count; $i++){
        $parameters += New-Object RS.ParameterValue
        $parameters[$i].Name  = "ParameterName"
        $parameters[$i].Value = "$($InputArray[$i])"
    }

    # Add the parameter array to the service.  Note that this returns some
    # information about the report that is about to be executed.
    $RS.SetExecutionParameters($parameters, "en-us") > $null
    
    # Render the report to a byte array. The first argument is the report format.
    $RenderOutput = $RS.Render('CSV',
        $deviceInfo,
        [ref] $extension,
        [ref] $mimeType,
        [ref] $encoding,
        [ref] $warnings,
        [ref] $streamIDs
    )

    $output = [System.Text.Encoding]::ASCII.GetString($RenderOutput)
    return $output
}
$InputArray = @('XXXXXX', 'YYYYYY', 'ZZZZZZ', 'ABCDEF')

<#
# The below code works perfectly
$Data = GetSSRSData -InputArray $InputArray
ConvertFrom-Csv -InputObject $Data
#>

$job = Start-Job -ScriptBlock $GetSSRSData -ArgumentList $InputArray
do { [System.Windows.Forms.Application]::DoEvents() } until ($job.State -ne "Running")
$Data = Receive-Job -Job $job
Write-Host $Data # returns only the first record

When I change the bottom part as shown below, I am able to verify that the job ends after the first record is output.

        $RenderOutput = $RS.Render('CSV',
        $deviceInfo,
        [ref] $extension,
        [ref] $mimeType,
        [ref] $encoding,
        [ref] $warnings,
        [ref] $streamIDs
    )

    Write-Output $RenderOutput
}

$InputArray = @('XXXXXX', 'YYYYYY', 'ZZZZZZ', 'ABCDEF')
$job = Start-Job -ScriptBlock $GetSSRSData -ArgumentList $InputArray
do {[System.Text.Encoding]::ASCII.GetString($job.ChildJobs[0].Output)} until ($job.State -ne "Running")

I also tried adding a sleep function of 5 seconds after the Render function, but it did not make any difference.

Please note that repeatedly calling the Start-Job for each input is not an option as each function call is costing a lot of time and hence the report needs to be pulled in a single call.

Ref: https://stackoverflow.com/a/63253699/4137016

Upvotes: 0

Views: 625

Answers (1)

Daniel
Daniel

Reputation: 5114

Answer is here: ArgumentList parameter in Invoke-Command don't send all array
Probably better answer here: How do I pass an array as a parameter to another script?

Change -ArgumentList $InputArray to -ArgumentList (,$InputArray)

$InputArray = @('XXXXXX', 'YYYYYY', 'ZZZZZZ', 'ABCDEF')
$job = Start-Job -ScriptBlock $GetSSRSData -ArgumentList (,$InputArray)

Upvotes: 1

Related Questions