Uploading SSRS Reports with PowerShell

The existing SSRS report deployment process I have been working with uses SQL Server's RS Utility along with a custom app that creates RSS input files that rs.exe reads. Both applications are used by a PowerShell script that serves as the driver.

In setting up a new machine at work I had neither tool installed initially and the dependencies started to bug me. Looking at the SSRS web service I noticed a CreateReport method so I created the below function to deploy the reports using PowerShell alone:

function UploadReports ($reportServerName = $(throw "reportServerName is required."), 
    $fromDirectory = $(throw "fromDirectory is required."), $serverPath = $(throw "serverPath is required."))
{
    Write-Output "Connecting to $reportServerName"
    $reportServerUri = "http://{0}/ReportServer/ReportService2005.asmx" -f $reportServerName
    $proxy = New-WebServiceProxy -Uri $reportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential
    
    Write-Output "Inspecting $fromDirectory"
    # coerce the return to be an array with the @ operator in case only one file
    $files = @(get-childitem $fromDirectory *.rdl -rec|where-object {!($_.psiscontainer)})
    
    $uploadedCount = 0
    
    foreach ($fileInfo in $files)
    {    
        $file = [System.IO.Path]::GetFileNameWithoutExtension($fileInfo.FullName)        
        $percentDone = (($uploadedCount/$files.Count) * 100)        
        Write-Progress -activity "Uploading to $reportServerName$serverPath" -status $file -percentComplete $percentDone
        Write-Output "%$percentDone : Uploading $file to $reportServerName$serverPath"
        $bytes = [System.IO.File]::ReadAllBytes($fileInfo.FullName)
        $warnings = $proxy.CreateReport($file, $serverPath, $true, $bytes, $null)
        
        if ($warnings)
        {
            foreach ($warn in $warnings)
            {
                Write-Warning $warn.Message
            }
        }
        
        $uploadedCount += 1
    }    
}

Calling the function is straightforward:

	UploadReports "report-server.domain.com" "c:\temp" "/TEST1/AppName"

What I am wondering now is why use the RS Utility at all if it is that easy to deploy reports using just PowerShell and the SSRS web service? My understanding is that rs.exe is just a thin wrapper around the web service, no? What does RS add? Using this method seemed to produce the same results without the extra dependencies but maybe I am missing something?

Updates

• 10/21/2011 - Updated script to coerce get-childitem result to be an array so this works if only uploading a single report

Batch Download SSRS Reports with PowerShell

Lately our continuous integration process has encountered some errors with automatic SSRS report deployments to our Test environment when reports are merged to the appropriate branch. Unfortunately when this fails the process makes no attempt to re-deploy the reports on the next build.

Those CI failures left me with a need to mass download the Dev and Test report definition files from the report server and compare the results. I found no way to batch download reports from Report Manager so I turned to PowerShell. I figured someone had done this before so a quick web search brought me to this post from which I based the script below. I made changes to turn the script into a function, added progress reporting and other output, and allowed specifying what server path to use and where to download the reports to. The resulting function follows.

function DownloadReports($reportServerName = $(throw "reportServerName is required."), 
    $baseDirectory = $(throw "baseDirectory is required."), $baseServerPath = "/")
{
    $status = "Downloading reports from {0} to {1}" -f $reportServerName, $baseDirectory
    Write-Output $status
    Write-Progress -activity "Connecting" -status $status -percentComplete -1
    
    [void][System.Reflection.Assembly]::LoadWithPartialName("System.Xml.XmlDocument");
    [void][System.Reflection.Assembly]::LoadWithPartialName("System.IO");
     
    $ReportServerUri = "http://{0}/ReportServer/ReportService2005.asmx" -f $reportServerName;
    $Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential ;
     
    #check out all members of $Proxy
    #$Proxy | Get-Member
    #http://msdn.microsoft.com/en-us/library/aa225878(v=SQL.80).aspx
     
    #second parameter means recursive
    $items = $Proxy.ListChildren($baseServerPath, $true) | `
             select Type, Path, ID, Name | `
             Where-Object {$_.type -eq "Report"};
     
    
    if(-not(Test-Path $baseDirectory))
    {
        [System.IO.Directory]::CreateDirectory($baseDirectory) | out-null
    }
    
    $downloadedCount = 0
    
    foreach($item in $items)
    {    
        #need to figure out if it has a folder name
        $subfolderName = split-path $item.Path;
        $reportName = split-path $item.Path -Leaf;
        $fullSubfolderName = $baseDirectory + $subfolderName;
        
        $percentDone = (($downloadedCount/$items.Count) * 100)        
        Write-Progress -activity ("Downloading from {0}{1}" -f $reportServerName, $subFolderName) -status $reportName -percentComplete $percentDone
        
        if(-not(Test-Path $fullSubfolderName))
        {
            #note this will create the full folder hierarchy
            [System.IO.Directory]::CreateDirectory($fullSubfolderName) | out-null
        }
     
        $rdlFile = New-Object System.Xml.XmlDocument;
        [byte[]] $reportDefinition = $null;
        $reportDefinition = $Proxy.GetReportDefinition($item.Path);        
     
        #note here we're forcing the actual definition to be 
        #stored as a byte array
        #if you take out the @() from the MemoryStream constructor, you'll 
        #get an error
        [System.IO.MemoryStream] $memStream = New-Object System.IO.MemoryStream(@(,$reportDefinition));
        $rdlFile.Load($memStream);
     
        $fullReportFileName = $fullSubfolderName + "\" + $item.Name +  ".rdl";
        #Write-Host $fullReportFileName;
        $rdlFile.Save( $fullReportFileName);
        "Downloaded {0}.rdl from {1}{2} to {3}  ({4:###}%)" -f $reportName, $reportServerName, $subfolderName, $fullSubfolderName, $percentDone
        $downloadedCount += 1
    }
    
    "Downloaded {0} reports from {1}{2} to {3}" -f $downloadedCount, $reportServerName, $subfolderName, $fullSubfolderName
}

Now a wrapper to download reports from 2 locations and then launch Explorer to view the results:

function DownloadAppNameDevAndTestReports
{
    $folderName = Get-Date -format "MMM-dd-yyyy-hhmm tt";
    $folder = Join-Path $env:temp $folderName
    DownloadReports "report-server.domain.com" $folder "/DEV1/AppName"
    DownloadReports "report-server.domain.com" $folder "/TEST1/AppName"
    
    ii $folder
}

At that point I can easily use a tool like SourceGear DiffMerge to compare the two folders for differences.