Powershell Script - New Version of my SSRS Bulk Upload

This Powershell script takes all report files(.rdl) in a folder ,creates the folder structure on the server ,uploads the reports and changes the data sources.

If you liked the first version of this script ,you'll love the new version.
The first version was written to solve a problem and nothing more, but turned out that it was something the  SSRS community was waiting for as it is one of the most read posts on my blog.

The new version has been completely rewritten to make it more robust and in the process i've added a feature or two.

• It now uses the Reporting Service 2010 Web Service
• It's now structurally a better script. Easier to understand and work with.
• Supports Folder structure
• Can easily be modified to upload all kinds of elements like images,pdf+

The script is designed for Configmgr reports ,but the only part that's specific to Configmgr is the part that searches for the data source. To work with other data sources you just need to modify the search criteria or just "hard code" in the name of the shared data source you want to use.

How It works

The new script has default values on some of the parameters to make it easier to use.
only required parameter is the URL(-webserviceUrl) to the reporting server.

& '.\Upload Reports v3.ps1' -webServiceUrl http://yourSSRSserver

Default it will look in the script folder for reports ,upload them to a folder called "Larsen Reports" and wont overwrite if report already exist ,but all this can be changed by setting additional parameters.

  & '.\Upload Reports v3.ps1' -webServiceUrl http://yourSSRSserver -SourceDirectory d\Reports  -ReportFolder Demo -overwrite $true

In this example i just threw a few reports in a folder/sub folder structure. Some reports on the root and some in the sub directories.

I run the script assigning the -reportFolder paramenter to Demo.

As you can see in the screenshot under it first creates the folder structure, then it searches for *.rdl files and upload them to the correct folder/ sub folder and changes the data source.

Everything should be ready to use!

Looking at the Report server, it has created the folders Demo and the 3 sub folders. The reports are uploaded in the right place.

..and the data source has been changed to the correct one.

Download: Upload Reports v3.zip

<#
.SYNOPSIS
    Bulk import SSRS Reports from a folder and update the reports data source.
.DESCRIPTION
    This script takes all the RDL files in a folder and imports them into a SSRS and updates the reports data source to the Configmgr shared data source.
.NOTES
    File Name  : Upload Reports.ps1
    Version    : 3.00 ,13.jun-2016
    Author     : Thomas Larsen - thomas.larsen@tromsfylke.no
    Requires   : Version 3
.LINK
    Blog
      http://larsenconfigmgr.blogspot.com

.PARAMETER webServiceUrl
Base URL to the report server ,usually the Configmgr Site server

.PARAMETER reportFolder
 Report Server folder where the reports are imported to, the script creates the folder if it's not there.
 
.PARAMETER SourceDirectory
The local folder where the .rdl files are located.

.PARAMETER Overwrite
Set to True if Script should overwrite Reports it they already exist.

.Example

& '.\Upload Reports.ps1' -webServiceUrl "http://Reportserver.domain.local"
Takes the report files from current directory(And Subdirectories) and uploads them the to a folder on SSRS called Larsen Reports(Default)

.Example

& '.\Upload Reports.ps1' -webServiceUrl "http://Reportserver.domain.local" -reportFolder "Larsen Reports -SourceDirectory "c:\ReportsToUpload" -overwrite $True
All Parameters ,select Sourcefolder Destination Folder ,Overwrite if exist


#>

Param(
   [string]$webServiceUrl,
      
   [string]$reportFolder = "Larsen Reports",

   [string]$SourceDirectory = $PSScriptRoot,

   [bool]$overwrite = $false
   )

Write-Host "Thomas Larsen - December 2015 - http://larsenconfigmgr.blogspot.com" -ForegroundColor Cyan
Write-Host "This Script is provided AS-IS, no warrenty is provided" 
Write-host ""

#Connect to SSRS
Write-Host "Reportserver: $webServiceUrl" -ForegroundColor DarkMagenta
Write-Host "Estabishing Proxy connection, connecting to : $webServiceUrl/ReportServer/ReportService2010.asmx?WSDL"
Write-Host ""

$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl'/ReportServer/ReportService2010.asmx?WSDL' -UseDefaultCredential

#######
#Get Configmgr shared datasource
$Items = $ssrsProxy.listchildren("/", $true) | where {$_.TypeName -eq "Datasource" }
foreach ($item in $items)
    {
    #Check to see if Datasource name patches Guid Pattern
    if ($item.name -match '{([a-zA-Z0-9]{8})-([a-zA-Z0-9]{4})-([a-zA-Z0-9]{4})-([a-zA-Z0-9]{4})-([a-zA-Z0-9]{12})}' -and $item.path -like '/ConfigMgr*')
        {
        Write-Host "Datasource:" $item.Name -ForegroundColor Magenta  
        Write-host "Type:" $item.TypeName 
        Write-Host "Path:" $item.Path

        #Save parameters for later use.
        $DatasourceName = $item.Name
        $DatasourcePath = $item.Path
        }
    }

function SSRSFolder ([string]$reportFolder,[string]$reportPath)
{
##########################################    
#Create Folder     
        try
        {
            $ssrsProxy.CreateFolder($reportFolder, $reportPath, $null) | out-null
            if($reportPath -eq '/')
            {
            Write-Host "Folder `"$reportpath$reportFolder`" Created"
            }
            else
            {
            Write-Host "Folder `"$reportpath/$reportFolder`" Created"
            }
        }
        catch [System.Web.Services.Protocols.SoapException]
        {
            if ($_.Exception.Detail.InnerText -match "rsItemAlreadyExists400")
            {
                Write-Host "Folder: $reportFolder already exists."
            }
            else
            {
                $msg = "Error creating folder: $reportFolder. Msg: '{0}'" -f $_.Exception.Detail.InnerText
                Write-Error $msg
            }
        }
}

Function SSRSItem ([string]$ItemType,$item,[string]$folder)
 {
 Write-host ""
 #ReportName
 if ($ItemType -ne "Resource")
 {
 $ItemName = [System.IO.Path]::GetFileNameWithoutExtension($item);
 }
 else
 {
 $ItemName = $item.Name
 }
 write-host $ItemName -ForegroundColor Green 

 #Upload File
     try
    {
        #Get Report content in bytes
        Write-Host "Getting file content of : $item"
        $byteArray = Get-Content $item.FullName -encoding byte
        $msg = "Size: {0} KB" -f "{0:N0}" -f ($byteArray.Length/1KB) 
        Write-Host $msg 
        
        Write-Host "Uploading to: $folder"
 
        #Sets property for images(only png)
        $type = $ssrsProxy.GetType().Namespace
        $datatype = ($type + '.Property')              
        $property =New-Object ($datatype);
        if ($ItemType -eq "Resource" -and $item -like "*.png")
        {
        $property.Name = "MimeType"
        $property.Value = “image/png”
        }
        else
        {
        $property = $null
        }

        #Call Proxy to upload report  
        $warnings =@();    
        $ssrsProxy.CreateCatalogItem($ItemType,$itemName,$folder,$overwrite,$byteArray,$property,[ref]$warnings) | out-null
        if($warnings.Length -le 1) { Write-Host "Upload Success." -ForegroundColor Green }
        else 
        {        
            foreach ($message in $warnings)
                {
                if ($message.Code -ne "rsDataSourceReferenceNotPublished")
                    {
                    write-host "$($message.Severity) $($message.Code) $($message.Message)" -ForegroundColor Yellow
                    }
                }
        }
    }
    catch [System.IO.IOException]
    {
        $msg = "Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message
        Write-Error msg
    }
    catch [System.Web.Services.Protocols.SoapException]
 {
    $caught = $false
            if ($_.Exception.Detail.InnerText -match 'rsItemAlreadyExists400')
            {
                $caught = $true
                Write-Host "Report: $itemName already exists." -ForegroundColor Red
            }
            if ($_.Exception.Detail.InnerText -match 'CoretechSSRS')
            {
                $caught = $true
                Write-Host "Cant't find Reporting Extention File." -ForegroundColor Red
            }
            elseif ($caught -eq $false)
            {
                $msg = "Error uploading report: $reportName. Msg: '{0}'" -f $_.Exception.Detail.InnerText
                Write-Error $msg
            }
  
 }
}

Function SSRSDatasource ([string]$ReportPath,$DataSourceName,$DataSourcePath)
{
$report = $ssrsProxy.GetItemDataSources($ReportPath)
ForEach ($Source in $report)
    {
    $proxyNamespace = $Source.GetType().Namespace
        $constDatasource = New-Object ("$proxyNamespace.DataSource")
        $constDatasource.Name = $DataSourceName
        $constDatasource.Item = New-Object ("$proxyNamespace.DataSourceReference")
        $constDatasource.Item.Reference = $DataSourcePath

   $Source.item = $constDatasource.Item
    $ssrsProxy.SetItemDataSources($ReportPath, $Source)
    Write-Host "Changing datasource `"$($Source.Name)`" to $($Source.Item.Reference)"
    }
}

##Create Folder Structure
#Create Base Folder

Write-Host "Creating Folders Structure:" -ForegroundColor DarkMagenta
SSRSFolder $reportFolder "/"

ForEach($Folder in Get-ChildItem $SourceDirectory -Directory)
    {
    #Add each folder in the sourcefolder to the reporting service 
    SSRSFolder $Folder.Name /$reportFolder
    }

     
#Upload Reports in root folder
ForEach ($rdlfile in Get-ChildItem $SourceDirectory -Filter *.rdl )
{
SSRSItem Report $rdlfile /$reportFolder

#Change Report Datasource
$ReportPath = "/" + $reportFolder + "/" +  $rdlfile.BaseName 
SSRSDatasource $ReportPath $DataSourceName $DataSourcePath
}

##For Each folder
 
ForEach($Folder in Get-ChildItem $SourceDirectory -Directory)
    {
    #Add each folder in the sourcefolder to the reporting service 
    #SSRSFolder $Folder.Name /$Basefolder 
    
    #Add reports in the folder
    ForEach ($rdlfile in Get-ChildItem $Folder -Filter *.rdl )
        {
        SSRSItem Report $rdlfile /$reportFolder/$($folder.Name) 

        #Change Report Datasource
        $ReportPath = "/" + $reportFolder + "/" + $folder.Name + "/" + $rdlfile.BaseName 
        SSRSDatasource $ReportPath $DataSourceName $DataSourcePath
        }
    }

OSD Details ,Part 2 of my OSD Dashboard Report

As promised, here is part 2 for my OS Deployment Dashboard.

It's designed to be a drill trough report from my OSD Dashboard,but you can also run it stand alone.
The query has been  completely rewritten from my last report so it should be a lot more reliable than the last version.

Download Reports.zip

To upload the report ,download the zipfile and extract to a folder. you can upload the rdl files the old fashion way or you can test drive the new version on my upload script.

To run the script you only need to specify the url to the reporting server(normally the site server)

The script will create a new folder called "Larsen Reports"(can be set with parameter), upload the reports and change the datasource to your configmgr server. 

& '.\Upload Reports v3.ps1' -webServiceUrl http://your-report-server 

Staring with the controls ,the "Deployments" field lists all task sequences and deployments in a readable format. Next the "Computer" will filter to only show computers that have run that deployment.

Lastly for the "Start Date" and "End Date" you just need to make sure the computers OS Deployment are between these dates. (These two parameters are mostly used by the Dashboard to select the correct deployment when there are several attempts for a single machine.) 

The top of the report shows some basic info about the machine, name ,DN and hardware model if they are available.

The part your most likely interested in is the step by step status for the OS Deployment.

It'll show you a nicely formatted (if i may say so myself:-) ) list  with the details for each step. in the task sequence and how long time each step took.

The list is quite minimalist ,but a lot of info is available as tooltips. Execution status on the Group And Action Name and Exit Code and Action Output on the Step number.

If a group or action is skipped the text is over lined to illustrate that. If an action has an exit code other than 0 (Success) the line is colored red so any errors should be easy to spot.

If you have any trouble displaying the graphics in the report ,try restarting the SSRS Service.

As always, hope you like the report. Post comments and questions below.

If you think others might find this usefull ,feel free to share a link on twitter ,facebook and forums. (this blog is tiny so not many people have seen this yet) 

Powershell Script - Bulk import SSRS Reports and update datasources

June 15: New version of this script available ,get it here:
new-version-of-my-ssrs-bulk-upload

For my first post of 2015 i give you a script that i wrote.
What this script does is it takes all .rdl files in a folder and imports them to your report server and changes the reports datasource to your Configmgr shared datasource.
This is useful if you need to import a lot of reports or if you're not that confident with SSRS.

The plan is to rework some of my old reports ,create some new ones and create a report pack that people easily can plug in to their Configmgr environment.
The report pack version one is going to contain some reports for Client Details  ,OS Deployment ,Application Deployement and Software Update so stay tuned!




To give you a preview to test with I've bundled it with my two of my Client Reports. The Make & Model Report posted here earlier and a Computer Reboot Report that I've revamped for the occasion.

This is a test ,so i would appreciate if you post bellow if you get it to work(or not).  

Download the Zip file and run the Script with the following parameters:
Download: Upload Reports.ps1

The Script takes 3 parameters:

• webserviceURL: The base URL of your report server. In many cases the site server.(Note! no /report at the end)
• Sourcefolder: Probably self explanatory ,but anyway... folder where your .rdl files are located
• Reportfolder:  The folder you want to create in SSRS and/or upload to

& '.\Upload Reports.ps1' -webServiceUrl "http://reportserver" -reportFolder "Larsen Reporting" -SourceDirectory "d:\Demo"

This should give you a folder in SSRS called "Larsen Reporting" with two reports in it. The reports should in theory be ready to run since the script automatically updates the datasource. 

Download: Upload Reports.ps1

<#
.SYNOPSIS
    Bulk import SSRS Reports from a folder and update the reports data source.
.DESCRIPTION
    This script takes all the RDL files in a folder and imports them into a SSRS and updates the reports data source to the Configmgr shared data source.
.NOTES
    File Name  : Upload Reports.ps1
    Version    : 1.03 ,05.jan-2015
    Author     : Thomas Larsen - thomas.larsen@tromsfylke.no
    Requires   : Version 3
.LINK
    Blog
      http://larsenconfigmgr.blogspot.com

.PARAMETER webServiceUrl
Base URL to the report server ,usualy the Configmgr Site server

.PARAMETER reportFolder
 Report Server folder where the reports are imported to, the script creates the folder if it's not there.
 
.PARAMETER SourceDirectory
The local folder where the .rdl files are located.

.Example
& '.\Upload Reports.ps1' -webServiceUrl "http://Reportserver.domain.local" -reportFolder "Larsen Reports -SourceDirectory "c:\ReportsToUpload"

#>
 

Param(
  [Parameter(Mandatory=$True)]
   [string]$webServiceUrl,
 
   [Parameter(Mandatory=$True)]
   [string]$reportFolder,

   [Parameter(Mandatory=$True)]
   [string]$SourceDirectory
   )

#$webServiceUrl = "http://reportserver.domain.local"
#$reportFolder = "Larsen Reports"
#$SourceDirectory = "C:\ReportstoUpload"

$ErrorActionPreference = "Continue"
Write-Host "Thomas Larsen - Januar 2015 - http://larsenconfigmgr.blogspot.com" -ForegroundColor Cyan
Write-Host "This Script is provided AS-IS, no warrenty is provided" 
Write-host ""

#Connect to SSRS
Write-Host "Reportserver: $webServiceUrl" -ForegroundColor Magenta
Write-Host "Creating Proxy, connecting to : $webServiceUrl/ReportServer/ReportService2005.asmx?WSDL"
Write-Host ""
$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl'/ReportServer/ReportService2005.asmx?WSDL' -UseDefaultCredential
$reportPath = "/"


#######
#Get Configmgr shared datasource
$Items = $ssrsProxy.listchildren("/", $true) | where {$_.Type -eq "Datasource" }
foreach ($item in $items)
{
#Check to see if Datasource name patches Guid Pattern
if ($item.name -match '{([a-zA-Z0-9]{8})-([a-zA-Z0-9]{4})-([a-zA-Z0-9]{4})-([a-zA-Z0-9]{4})-([a-zA-Z0-9]{12})}' -and $item.path -like '/ConfigMgr*')
{
Write-Host "Datasource:" $item.Name -ForegroundColor Magenta  
Write-host "Type:" $item.Type 
Write-Host "Path:" $item.Path

#Save parameters for later use.
$DatasourceName = $item.Name
$DatasourcePath = $item.Path
}}


##########################################    
#Create Folder
        Write-host ""
        try
        {
            $ssrsProxy.CreateFolder($reportFolder, $reportPath, $null)
            Write-Host "Created new folder: $reportFolder"
        }
        catch [System.Web.Services.Protocols.SoapException]
        {
            if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
            {
                Write-Host "Folder: $reportFolder already exists."
            }
            else
            {
                $msg = "Error creating folder: $reportFolder. Msg: '{0}'" -f $_.Exception.Detail.InnerText
                Write-Error $msg
            }
        }


#############################
#For each RDL file in Folder

foreach($rdlfile in Get-ChildItem $SourceDirectory -Filter *.rdl)
{
Write-host ""


#ReportName
 $reportName = [System.IO.Path]::GetFileNameWithoutExtension($rdlFile);
 write-host $reportName -ForegroundColor Green 
 #Upload File
     try
    {
        #Get Report content in bytes
        Write-Host "Getting file content of : $rdlFile"
        $byteArray = gc $rdlFile.FullName -encoding byte
        $msg = "Total length: {0}" -f $byteArray.Length
        Write-Host $msg
 
        $reportFolder = $reportPath + $reportFolder
        Write-Host "Uploading to: $reportFolder"
 
        #Call Proxy to upload report
        $warnings = $ssrsProxy.CreateReport($reportName,$reportFolder,$force,$byteArray,$null) 
        if($warnings.Length -le 1) { Write-Host "Upload Success." -ForegroundColor Green }
        else { write-host $warnings | % { Write-Warning "Warning: $_" }}
  
    }
    catch [System.IO.IOException]
    {
        $msg = "Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message
        Write-Error msg
    }
    catch [System.Web.Services.Protocols.SoapException]
 {
            if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
            {
                Write-Error "Report: $reportName already exists." 
            }
            else
            {
                $msg = "Error uploading report: $reportName. Msg: '{0}'" -f $_.Exception.Detail.InnerText
                Write-Error $msg
            }
  
 }



##Change Datasource
$rep = $ssrsProxy.GetItemDataSources($reportFolder+"/"+$reportName)
$rep | ForEach-Object {
$proxyNamespace = $_.GetType().Namespace
    $constDatasource = New-Object ("$proxyNamespace.DataSource")
    $constDatasource.Name = $DataSourceName
    $constDatasource.Item = New-Object ("$proxyNamespace.DataSourceReference")
    $constDatasource.Item.Reference = $DataSourcePath

$_.item = $constDatasource.Item
$ssrsProxy.SetItemDataSources($reportFolder+"/"+$reportName, $_)
Write-Host "Changing datasource `"$($_.Name)`" to $($_.Item.Reference)"
}

#Something in the foreach loop keeps adding forward slashes to the parameter evertime it runs. 
#Strips slashes from the parameter.  
$reportFolder = $reportFolder -replace "/",""
}

Write-host ""
Write-host "Finished" -ForegroundColor Magenta
Write-host ""