PowerShell – Working With SQL Server Reporting Services (SSRS) – Get/Set Data Sources & Deploy Reports

I am working on a DevOps deployment tool to automate all DB infrastructure related deployments (in PowerShell). It has a SSRS side to it besides other components. i.e., SSRS reports have to be deployed.

The functions at a high level

In this post, I want to publish a few functions that I created around SSRS. They are related to and depend on each other.

  • Get-SSRS – Given the SSRS URI returns the WSDL endpoint
  • Get-SSRSReport – Returns one or more reports based on inputs
  • Get-SSRSSharedDataSource – Returns one or more shared data sources based on inputs
  • Get-SSRSReportDataSource – Returns the data source information on a report by report basis based on inputs
  • Set-SSRSReportDataSource – Sets the data source of a report to the given data source.
  • Install-SSRS – Deploys an SSRS report to a specific folder and also optionally sets the datasource for the deployed report

My reference

A huge thank you goes to Ivan Josipovic from whose posts and functions I created my own. Here are the links I referenced:

The functions:

As I mentioned, the functions are related to each other. So, you would have to dot-source all of them even if you wanted the functionality in just one function.

In know that they very light on explanation but I will try to circle around and add descriptions. For now, I think you would appreciate the code being available even without the explanation.

Check out the usage examples at the top of each function for reference on how to use the functions.

CAUTION: WordPress messes-up PowerShell block comments. So, please fix the comment at the top of each script.

Get-SSRS


#######################
#
.SYNOPSIS
    Gets the SSRS reference

.DESCRIPTION
    The main SSRS reference from which all other things need to be done

.INPUTS
    The report server URI 

.OUTPUTS
    Returns SSRS reference

.EXAMPLE 

    Get-SSRS `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx'

.NOTES 

    Based on http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell

Version History
    v1.0   - Jana Sattainathan - Apr.24.2017

.LINK
    N/A
#
function Global:Get-SSRS
{
    [CmdletBinding()]
    param( 

        [Parameter(Mandatory=$true)]
        [string] $ReportServerUri

    )

    [string] $fn = $MyInvocation.MyCommand
    [string] $stepName = "Begin [$fn]"    

    [string] $reportServerURIOverride = $ReportServerUri
    [object] $returnObj = $null

    try
    {        

        $stepName = "[$fn]: Validate parameters"
        #--------------------------------------------
        Write-Verbose $stepName  

        #Append the "?wsdl" at the end if it does not already have it
        # e.g., 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx?wsdl'

        if (-not $reportServerURIOverride.EndsWith('?wsdl'))
        {
            $reportServerURIOverride = ("$reportServerURIOverride"+'?wsdl')
        }

        $stepName = "[$fn]: Open SSRS url"
        #--------------------------------------------
        Write-Verbose $stepName  

        $URI = New-Object System.Uri($ReportServerUri)
        $SSRS = New-WebServiceProxy -Uri $URI -UseDefaultCredential

        $returnObj = $SSRS

        #Return value
        ,$returnObj        

    }
    catch
    {
        [Exception]$ex = $_.Exception
        Throw "Unable to get SSRS reference. Error in step: `"{0}]`" `n{1}" -f `
                        $stepName, $ex.Message
    }
    finally
    {
        #Return value if any
    }
}

Get-SSRSReport


#
.SYNOPSIS
    Gets the SSRS data sources given an URI

.DESCRIPTION
    Gets the list of all data sources or a specific datasource based on supplied parameters

.INPUTS
    The report server URI and optionally a datasource

.OUTPUTS
    Returns data sources

.EXAMPLE
    #Gets a specific report
    Get-SSRSReport `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx' `
            -ReportFolderPath '/Technical Reports' `
            -ReportName 'SubProducts'

.EXAMPLE
    #Gets a all reports in a folder
    Get-SSRSReport `
            -ReportServerUri 'http://MYSERVER/MYInstReportServer/ReportService2010.asmx' `
            -ReportFolderPath '/Technical Reports'

.EXAMPLE
    #Gets all reports in all folders
    Get-SSRSReport `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx'

.EXAMPLE
    #Gets all reports in all folders and counts the number of datasources

    $ReportServerUri = 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx'
    $SSRS = Get-SSRS -ReportServerUri $ReportServerUri

    $reports = Get-SSRSReport `
                 -ReportServerUri $ReportServerUri

    foreach($report in $reports)
    {

        $dataSources = $SSRS.GetItemDataSources($report.Path)

        "Data sources for [{0}]" -f $report.Path
        $dataSources.Name

        "----------------------------------------------"
    }

.NOTES 

    Based on http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell

Version History
    v1.0   - Jana Sattainathan - Apr.21.2017

.LINK
    N/A
#

function Global:Get-SSRSReport
{
    [CmdletBinding()]
    param( 

        [Parameter(Mandatory=$true)]
        [string] $ReportServerUri,

        [Parameter(Mandatory=$false)]
        [string] $ReportFolderPath = '/',

        [Parameter(Mandatory=$false)]
        [string] $ReportName = ''

    )

    [string] $fn = $MyInvocation.MyCommand
    [string] $stepName = "Begin [$fn]"    

    #-----------------------------------
    #We need to collect all these inputs!
    #-----------------------------------
    [object[]] $returnObj = @()

    try
    {        

        $stepName = "[$fn]: Validate parameters"
        #--------------------------------------------
        Write-Verbose $stepName  

        $stepName = "[$fn]: Open SSRS url"
        #--------------------------------------------
        Write-Verbose $stepName  

        $SSRS = Get-SSRS -ReportServerUri $ReportServerUri

        $stepName = "[$fn]: Get all the reports"
        #--------------------------------------------
        Write-Verbose $stepName  

        $reports = $SSRS.ListChildren($ReportFolderPath, $true) |
                                Where-Object {$_.TypeName -eq 'Report'}

        #Result will be of the form (first element):
        <#         $reports[0]             ID                    : 0b767817-1c39-4a86-98e5-ab372f80dc64             Name                  : MyReport             Path                  : /MyApp/MyReport             VirtualPath           :              TypeName              : Folder             Size                  : 0             SizeSpecified         : False             Description           :              Hidden                : False             HiddenSpecified       : False             CreationDate          : 1/14/2015 11:11:43 AM             CreationDateSpecified : True             ModifiedDate          : 1/14/2015 11:11:59 AM             ModifiedDateSpecified : True             CreatedBy             : MYUSERID             ModifiedBy            : MYUSERID             ItemMetadata          : {}         #>

        $stepName = "[$fn]: Filter to specific report among the reports"
        #--------------------------------------------
        Write-Verbose $stepName  

        if ($ReportName.Trim().Length -gt 0)
        {
            <#             #Combine the folder and the report name to form full path (without the file extension if any)             $reportFullPath =  Join-Path `                                 -Path $ReportFolderPath `                                 -ChildPath ([system.io.fileinfo]$ReportName).BaseName             #>

            #Not doing it with the full path would allow us to search all reports in all paths by name starting from the root!
            $report = ($reports |
                            Where-Object {$_.Name -eq ([system.io.fileinfo]$ReportName).BaseName}) #Eliminate file extension

            $returnObj = @($report)
        }
        else
        {
            $returnObj = $reports
        }

        #Return value
        ,$returnObj        

    }
    catch
    {
        [Exception]$ex = $_.Exception
        Throw "Unable to get SSRS reports. Error in step: `"{0}]`" `n{1}" -f `
                        $stepName, $ex.Message
    }
    finally
    {
        #Return value if any
    }
}

Get-SSRSSharedDataSource

#
.SYNOPSIS
    Gets the SSRS data sources given an URI

.DESCRIPTION
    Gets the list of all data sources or a specific datasource based on supplied parameters

.INPUTS
    The report server URI and optionally a datasource

.OUTPUTS
    Returns data sources

.EXAMPLE 

    #Get a specific data source
    Get-SSRSSharedDataSource `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx' `
            -DataSourcePath '/' `
            -DataSourceName 'DataMart Reporting Services'

.EXAMPLE 

    #Get a specific data source in a specific path
    Get-SSRSSharedDataSource `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx' `
            -DataSourcePath '/Data Sources'

.EXAMPLE 

    #Get all data sources
    Get-SSRSSharedDataSource `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx'

.NOTES 

    Based on http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell

Version History
    v1.0   - Jana Sattainathan - Apr.21.2017

.LINK
    N/A
#

function Global:Get-SSRSSharedDataSource
{
    [CmdletBinding()]
    param( 

        [Parameter(Mandatory=$true)]
        [string] $ReportServerUri,

        [Parameter(Mandatory=$false)]
        [string] $DataSourcePath = '/',

        [Parameter(Mandatory=$false)]
        [string] $DataSourceName = ''

    )

    [string] $fn = $MyInvocation.MyCommand
    [string] $stepName = "Begin [$fn]"    

    #-----------------------------------
    #We need to collect all these inputs!
    #-----------------------------------
    [string] $dataSourcePathOverride = $DataSourcePath
    [object[]] $returnObj = @()

    try
    {        

        $stepName = "[$fn]: Validate parameters"
        #--------------------------------------------
        Write-Verbose $stepName  

        $stepName = "[$fn]: Open SSRS url"
        #--------------------------------------------
        Write-Verbose $stepName  

        $SSRS = Get-SSRS -ReportServerUri $ReportServerUri

        $stepName = "[$fn]: Get all the datasources"
        #--------------------------------------------
        Write-Verbose $stepName  

        if ($dataSourcePathOverride.Trim().Length -eq 0)
        {
            $dataSourcePathOverride = '/'  #Could use "/Data Sources" but there could be data sources in other locations!
        }

        #Get the datasources
        $dataSources = $SSRS.ListChildren($dataSourcePathOverride, $true) |
                                Where-Object {$_.TypeName -eq 'DataSource'}

        #$dataSources = $SSRS.GetItemDataSources($dataSourcePathOverride)

        #Result will be of the form (first element):
        <#         $DataSources[0]             ID                    : b58a4feb-c3ad-49c8-a699-a4f52885d09d             Name                  : My DataSourceName             Path                  : /Data Sources/MyDataSourceName             VirtualPath           :              TypeName              : DataSource             Size                  : 467             SizeSpecified         : True             Description           :              Hidden                : False             HiddenSpecified       : False             CreationDate          : 1/14/2015 11:11:43 AM             CreationDateSpecified : True             ModifiedDate          : 2/25/2015 12:14:16 PM             ModifiedDateSpecified : True             CreatedBy             : domain\SOMEUSER             ModifiedBy            : domain\SOMEUSER             ItemMetadata          : {}         #>

        $stepName = "[$fn]: Filter to specific datasource if one was asked for"
        #--------------------------------------------
        Write-Verbose $stepName  

        if ($DataSourceName.Trim().Length -gt 0)
        {
            $dataSource = ($dataSources |
                            Where-Object {$_.Name -eq $DataSourceName}) 

            $returnObj = @($dataSource)
        }
        else
        {
            $returnObj = $dataSources
        }

        #Return value
        ,$returnObj

    }
    catch
    {
        [Exception]$ex = $_.Exception
        Throw "Unable to get SSRS data sources. Error in step: `"{0}]`" `n{1}" -f `
                        $stepName, $ex.Message
    }
    finally
    {
        #Return value if any
    }
}

Get-SSRSReportDataSource

#
.SYNOPSIS
    Gets the SSRS data sources for a give report or for all reports

.DESCRIPTION
    Given the URI and optionally a report folder path and/or report, gets the data sources

.INPUTS
    The report server URI and optionally report path and names

.OUTPUTS
    Returns data sources

.EXAMPLE
    #Gets data sources for a specific report
    Get-SSRSReportDataSource `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx' `
            -ReportFolderPath '/Technical Reports' `
            -ReportName 'SubProducts'

.EXAMPLE
    #Gets data sources of all reports in a folder
    Get-SSRSReportDataSource `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx' `
            -ReportFolderPath '/Technical Reports'

.EXAMPLE
    #Gets data sources of all reports in all folders
    Get-SSRSReportDataSource `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx'

.NOTES 

    Based on http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell

Version History
    v1.0   - Jana Sattainathan - Apr.25.2017

.LINK
    N/A
#

function Global:Get-SSRSReportDataSource
{
    [CmdletBinding()]
    param( 

        [Parameter(Mandatory=$true)]
        [string] $ReportServerUri,

        [Parameter(Mandatory=$false)]
        [string] $ReportFolderPath = '/',

        [Parameter(Mandatory=$false)]
        [string] $ReportName = ''

    )

    [string] $fn = $MyInvocation.MyCommand
    [string] $stepName = "Begin [$fn]"    

    #-----------------------------------
    #We need to collect all these inputs!
    #-----------------------------------
    [object[]] $returnObj = @()

    try
    {        

        $stepName = "[$fn]: Validate parameters"
        #--------------------------------------------
        Write-Verbose $stepName  

        $stepName = "[$fn]: Open SSRS url"
        #--------------------------------------------
        Write-Verbose $stepName  

        $SSRS = Get-SSRS -ReportServerUri $ReportServerUri

        $stepName = "[$fn]: Get all the reports"
        #--------------------------------------------
        Write-Verbose $stepName  

        $reports = Get-SSRSReport `
                     -ReportServerUri $ReportServerUri `
                     -ReportFolderPath $ReportFolderPath `
                     -ReportName $ReportName

        foreach($report in $reports)
        {
            $psObjectDS = New-Object PSObject
            $dataSources = $SSRS.GetItemDataSources($report.Path)

            #Put the report and data source info together
            $psObjectDS | Add-Member -NotePropertyName 'ReportPath' -NotePropertyValue $report.Path
            $psObjectDS | Add-Member -NotePropertyName 'ReportName' -NotePropertyValue $report.Name
            $psObjectDS | Add-Member -NotePropertyName 'DataSourceName' -NotePropertyValue $dataSources[0].Name
            $psObjectDS | Add-Member -NotePropertyName 'DataSourceReference' -NotePropertyValue $dataSources[0].Item.Reference

            #Return value
            $psObjectDS
        }

    }
    catch
    {
        [Exception]$ex = $_.Exception
        Throw "Unable to get SSRS report data sources. Error in step: `"{0}]`" `n{1}" -f `
                        $stepName, $ex.Message
    }
    finally
    {
        #Return value if any
    }
}

Set-SSRSReportDataSource

#
.SYNOPSIS
    Gets the SSRS data sources given an URI

.DESCRIPTION
    Gets the list of all data sources or a specific datasource based on supplied parameters

.INPUTS
    The report server URI and optionally a datasource

.OUTPUTS
    Returns data sources

.EXAMPLE 

    Set-SSRSReportDataSource `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx' `
            -ReportFolderPath '/Technical Reports' `
            -ReportName 'Subproducts' `
            -DataSourcePath '/Data Sources' `
            -DataSourceName 'DataMart Reporting Services'

.NOTES 

    Based on http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell

Version History
    v1.0   - Jana Sattainathan - Apr.21.2017

.LINK
    N/A
#

function Global:Set-SSRSReportDataSource
{
    [CmdletBinding()]
    param( 

        [Parameter(Mandatory=$true)]
        [string] $ReportServerUri,

        [Parameter(Mandatory=$false)]
        [string] $ReportFolderPath = '/',

        [Parameter(Mandatory=$true)]
        [string] $ReportName,

        [Parameter(Mandatory=$false)]
        [string] $DataSourcePath = '/',

        [Parameter(Mandatory=$true)]
        [string] $DataSourceName

    )

    [string] $fn = $MyInvocation.MyCommand
    [string] $stepName = "Begin [$fn]"    

    [HashTable]$params = @{
            'ReportServerUri' = $ReportServerUri;
            'ReportFolderPath' = $ReportFolderPath;
            'ReportName' = $ReportName;
            'DataSourcePath' = $DataSourcePath;
            'DataSourceName' = $DataSourceName}

    #-----------------------------------
    #We need to collect all these inputs!
    #-----------------------------------
    [string] $reportServerURIOverride = $ReportServerUri    

    try
    {        

        $stepName = "[$fn]: Validate parameters"
        #--------------------------------------------
        Write-Verbose $stepName  

        $stepName = "[$fn]: Get data source: [{0}]" -f $DataSourceName
        #--------------------------------------------
        Write-Verbose $stepName  

        $dataSource = Get-SSRSSharedDataSource `
                        -ReportServerUri $ReportServerUri `
                        -DataSourcePath $DataSourcePath `
                        -DataSourceName $DataSourceName

        if (($dataSource.Count -eq 0) `
                -or ($dataSource.Count -gt 1))
        {
            Throw "[$fn]: Found no matches or multiple data source matches for parameters: [{0}]" -f ($params | Out-String)
        }

        $stepName = "[$fn]: Get report"
        #--------------------------------------------
        Write-Verbose $stepName  

        $report = Get-SSRSReport `
                        -ReportServerUri $ReportServerUri `
                        -ReportFolderPath $ReportFolderPath `
                        -ReportName $ReportName

        if (($report.Count -eq 0) `
                -or ($report.Count -gt 1))
        {
            Throw "[$fn]: Found no matches or multiple report matches for parameters: [{0}]" -f ($params | Out-String)
        }

        $stepName = "[$fn]: Open SSRS url"
        #--------------------------------------------
        Write-Verbose $stepName  

        $SSRS = Get-SSRS -ReportServerUri $ReportServerUri

        $stepName = "[$fn]: Update the data source"
        #--------------------------------------------
        Write-Verbose $stepName  

        #https://blogs.infosupport.com/managing-ssrs-reports-with-powershell/
        #http://powershell-with-dave.blogspot.com/2014/08/editing-ssrs-shared-data-sources-with.html

        #The most useful link that finally helped me make this work!
        #http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell

        $dataSources = $SSRS.GetItemDataSources($report[0].Path)

        $dataSources | ForEach-Object {
            $proxyNamespace = $SSRS.GetType().Namespace

            $dataSourceNew =  New-Object ("$proxyNamespace.DataSource")                   #New-Object SSRS.DataSource
            $dataSourceNew.Name = $dataSource[0].Name
            $dataSourceNew.Item = New-Object ("$proxyNamespace.DataSourceReference")      #New-Object SSRS.DataSourceReference
            $dataSourceNew.Item.Reference = $dataSource[0].Path

            $_.Item = $dataSourceNew.Item
            $SSRS.SetItemDataSources($report[0].Path, $_)

            #$SSRS.SetItemDataSources($report[0].Path, @($dataSourceNew))
        }

        <#         #The above method did not work initially so tried this but this did not work either! Left here for reference in case we need to more things!         #http://stackoverflow.com/questions/36334676/deploying-ssrs-rdl-files-from-vb-net-issue-with-shared-datasources?rq=1         $itemRef = New-Object ("$proxyNamespace.ItemReference")         $itemRef.Name = $dataSource[0].Name         $itemRef.Reference = $dataSource[0].Path         $SSRS.SetItemReferences($report[0].Path, @($itemRef))         #>

        #Return value        

    }
    catch
    {
        [Exception]$ex = $_.Exception
        Throw "Unable to set SSRS report data source. Error in step: `"{0}]`" `n{1}" -f `
                        $stepName, $ex.Message
    }
    finally
    {
        #Return value if any
    }
}

Set-SSRSReportDataSource

#
.SYNOPSIS
    Installs a MSSQL report file to a folder

.DESCRIPTION
    Deploys the .rdl into the given folder using the report server URI

.INPUTS
    The report server URI/target folder/input rdl/log file

.OUTPUTS
    Returns all the details about the execution

.EXAMPLE 

    Install-SSRS `
            -ReportServerUri 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx' `
            -TargetFolder '/Technical Reports' `
            -InputFile '\\MyShare\Ver_1.1\SSRS\SubProducts.rdl' `
            -LogFile 'c:\~tmp\mysql.sql.out'

.NOTES 

    New function to install a MSSQL report file (.rdl) for a release 

Version History
    v1.0   - Jana Sattainathan - Mar.6.2017

.LINK
    N/A
#

function Global:Install-SSRS
{
    [CmdletBinding()]
    param( 

        [Parameter(Mandatory=$true)]
        [string] $ReportServerUri,

        [Parameter(Mandatory=$true)]
        [string] $TargetFolder,

        [Parameter(Mandatory=$false)]
        [string] $DataSource = '',

        [ValidateScript({Test-Path $_ -PathType Leaf -Include '*.rdl'})]
        [Parameter(Mandatory=$true)]
        [string] $InputFile,

        [Parameter(Mandatory=$true)]
        [string] $LogFile,

        [Parameter(Mandatory=$false)]
        [switch] $IgnoreWarnings = $false

    )

    [string] $fn = $MyInvocation.MyCommand
    [string] $stepName = "Begin [$fn]"
    [string] $errorMessage = ''
    [string] $command = 'N/A'
    [string] $commandOutput = ''
    [string] $reportName = [System.IO.Path]::GetFileNameWithoutExtension($InputFile)
    [System.Byte[]] $rdlBytes = [System.IO.File]::ReadAllBytes($InputFile) # The byte array of the file
    $warnings = $null # output warnings
    [string] $warningMessage = ''
    [string] $dataSourcePath = ''
    [string] $dataSourceName = ''

    [HashTable]$params = @{
            'ReportServerUri' = $ReportServerUri;
            'TargetFolder' = $TargetFolder;
            'InputFile' = $InputFile;
            'LogFile' = $LogFile
            }

    try
    {        

        $stepName = "[$fn]: Validate parameters"
        #--------------------------------------------
        Write-Verbose $stepName         

        $stepName = "[$fn]: Run the command and collect output"
        #--------------------------------------------
        Write-Verbose $stepName          

        #Create an empty log file
        New-Item -Path  $LogFile -Type 'File' -Force >$null

        #Run the command
        try
        {

            $stepName = "[$fn]: Create Instance of Web Service instance"
            #--------------------------------------------
            Write-Verbose $stepName          

            $rs = New-WebServiceProxy `
                                    -Uri $ReportServerUri `
                                    -UseDefaultCredential `
                                    -Namespace SSRS.ReportingService2010

            $stepName = "[$fn]: Read the report file as byte array"
            #--------------------------------------------
            Write-Verbose $stepName          

            $bytes = [System.IO.File]::ReadAllBytes($InputFile)

            $stepName = "[$fn]: Deploy the report"
            #--------------------------------------------
            Write-Verbose $stepName          

            $report = $rs.CreateCatalogItem(
                            'Report',         # Catalog item type
                            $reportName,      # Report name
                            $TargetFolder,    # Destination folder
                            $true,            # Overwrite report if it exists?
                            $rdlBytes,        # .rdl file contents
                            $null,            # Properties to set.
                            [ref]$warnings)   # Warnings that occured while uploading.

            $stepName = "[$fn]: Collect warnings"
            #--------------------------------------------
            Write-Verbose $stepName          

            if ($IgnoreWarnings -eq $true)
            {
                if ($warnings.message)
                {
                    $warningMessage += "`nWarnings (if any), were supressed!"
                }
            }
            else
            {
                if ($warnings.message)
                {
                    $warnings | ForEach-Object -Process {
                        $warningMessage += "`nWarning: {0}" -f $_.Message
                    }
                }
            }

            $stepName = "[$fn]: Override the Data Source"
            #--------------------------------------------
            Write-Verbose $stepName          

            if ($DataSource.Trim().Length -gt 0)
            {
                if ($DataSource.Contains('/'))
                {
                    #Searches for the data source within this folder and sub-folders
                    $dataSourcePath = (Split-Path $DataSource -Parent)
                    $dataSourceName = (Split-Path $DataSource -Leaf)
                }
                else
                {
                    #Starts with the root and recursively searches for the data source
                    $dataSourcePath = '/'
                    $dataSourceName = $DataSource
                }

                Set-SSRSReportDataSource `
                        -ReportServerUri $ReportServerUri `
                        -ReportFolderPath $TargetFolder `
                        -ReportName $reportName `
                        -DataSourcePath $dataSourcePath `
                        -DataSourceName $dataSourceName
            }

            $exitCode = 0  #Successful
            $commandOutput = 'Report: [{0}] was deployed successfully. {1}' -f $reportName, $warningMessage
        }
        catch
        {
            $exitCode = -1  #UnSuccessful

            $commandOutput = "ERROR DEPLOYING REPORT: [{0}]. `n{1}." -f
                                        $reportName, $_.Exception.Message
        }
        finally
        {
            #Write command output to log file (whether successful, w/warnings or w/error)
            Out-File -FilePath $LogFile -InputObject $commandOutput -Encoding 'ASCII' -Append
        }

        $stepName = "[$fn]: Create return object with output"
        #--------------------------------------------
        Write-Verbose $stepName          

        $psObjectOutput = New-Object PSObject

        $psObjectOutput | Add-Member -NotePropertyName 'TargetType' -NotePropertyValue 'SSRS'
        $psObjectOutput | Add-Member -NotePropertyName 'InputFile' -NotePropertyValue $InputFile
        $psObjectOutput | Add-Member -NotePropertyName 'LogFile' -NotePropertyValue $LogFile
        $psObjectOutput | Add-Member -NotePropertyName 'Command' -NotePropertyValue $command
        $psObjectOutput | Add-Member -NotePropertyName 'CommandType' -NotePropertyValue 'SSRS.ReportingService2010-WebProxy'
        $psObjectOutput | Add-Member -NotePropertyName 'CommandOutput' -NotePropertyValue $commandOutput
        $psObjectOutput | Add-Member -NotePropertyName 'ExitCode' -NotePropertyValue $exitCode

        $stepName = "[$fn]: Return the output"
        #--------------------------------------------
        Write-Verbose $stepName  

        $psObjectOutput

    }
    catch
    {
        [Exception]$ex = $_.Exception
        Throw "Unable to deploy SSRS report. Error in step: `"{0}]`" `n{1}" -f `
                        $stepName, $ex.Message
    }
    finally
    {
        #Return value if any

    }

}

It took me a while to figure out the nuances and get this working exactly the way I want and I hope that it would save you a ton of time too. Please post your comments and also corrections if you make changes. Perhaps GitHub would be a better place for this code!

Advertisements

2 thoughts on “PowerShell – Working With SQL Server Reporting Services (SSRS) – Get/Set Data Sources & Deploy Reports

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s