Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PowerShell scripts to deploy a SQL Server Reporting Services project (*.rptproj) to a Reporting Server
#requires -version 2.0
[CmdletBinding()]
param (
[parameter(Mandatory=$true)]
[ValidatePattern('\.rptproj$')]
[ValidateScript({ Test-Path -PathType Leaf -Path $_ })]
[string]
$Path,
[parameter(
ParameterSetName='Configuration',
Mandatory=$true)]
[string]
$Configuration,
[parameter(
ParameterSetName='Target',
Mandatory=$true)]
[ValidatePattern('^https?://')]
[string]
$ServerUrl,
[parameter(
ParameterSetName='Target',
Mandatory=$true)]
[string]
$Folder,
[parameter(
ParameterSetName='Target',
Mandatory=$true)]
[string]
$DataSourceFolder,
[parameter(ParameterSetName='Target')]
[switch]
$OverwriteDataSources,
[System.Management.Automation.PSCredential]
$Credential
)
function New-XmlNamespaceManager ($XmlDocument, $DefaultNamespacePrefix) {
$NsMgr = New-Object -TypeName System.Xml.XmlNamespaceManager -ArgumentList $XmlDocument.NameTable
$DefaultNamespace = $XmlDocument.DocumentElement.GetAttribute('xmlns')
if ($DefaultNamespace -and $DefaultNamespacePrefix) {
$NsMgr.AddNamespace($DefaultNamespacePrefix, $DefaultNamespace)
}
return ,$NsMgr # unary comma wraps $NsMgr so it isn't unrolled
}
function Normalize-SSRSFolder (
[string]$Folder
) {
if (-not $Folder.StartsWith('/')) {
$Folder = '/' + $Folder
}
return $Folder
}
function New-SSRSFolder (
$Proxy,
[string]
$Name
) {
Write-Verbose "New-SSRSFolder -Name $Name"
$Name = Normalize-SSRSFolder -Folder $Name
if ($Proxy.GetItemType($Name) -ne 'Folder') {
$Parts = $Name -split '/'
$Leaf = $Parts[-1]
$Parent = $Parts[0..($Parts.Length-2)] -join '/'
if ($Parent) {
New-SSRSFolder -Proxy $Proxy -Name $Parent
} else {
$Parent = '/'
}
$Proxy.CreateFolder($Leaf, $Parent, $null)
}
}
function New-SSRSDataSource (
$Proxy,
[string]$RdsPath,
[string]$Folder,
[switch]$Overwrite
) {
Write-Verbose "New-SSRSDataSource -RdsPath $RdsPath -Folder $Folder"
$Folder = Normalize-SSRSFolder -Folder $Folder
[xml]$Rds = Get-Content -Path $RdsPath
$ConnProps = $Rds.RptDataSource.ConnectionProperties
$Definition = New-Object -TypeName SSRS.ReportingService2005.DataSourceDefinition
$Definition.ConnectString = $ConnProps.ConnectString
$Definition.Extension = $ConnProps.Extension
if ([Convert]::ToBoolean($ConnProps.IntegratedSecurity)) {
$Definition.CredentialRetrieval = 'Integrated'
}
$DataSource = New-Object -TypeName PSObject -Property @{
Name = $Rds.RptDataSource.Name
Path = $Folder + '/' + $Rds.RptDataSource.Name
}
if ($Overwrite -or $Proxy.GetItemType($DataSource.Path) -eq 'Unknown') {
$Proxy.CreateDataSource($DataSource.Name, $Folder, $Overwrite, $Definition, $null)
}
return $DataSource
}
$script:ErrorActionPreference = 'Stop'
Set-StrictMode -Version Latest
$PSScriptRoot = $MyInvocation.MyCommand.Path | Split-Path
$Path = $Path | Convert-Path
$ProjectRoot = $Path | Split-Path
[xml]$Project = Get-Content -Path $Path
if ($PSCmdlet.ParameterSetName -eq 'Configuration') {
$Config = & $PSScriptRoot\Get-SSRSProjectConfiguration.ps1 -Path $Path -Configuration $Configuration
$ServerUrl = $Config.ServerUrl
$Folder = $Config.Folder
$DataSourceFolder = $Config.DataSourceFolder
$OverwriteDataSources = $Config.OverwriteDataSources
}
$Folder = Normalize-SSRSFolder -Folder $Folder
$DataSourceFolder = Normalize-SSRSFolder -Folder $DataSourceFolder
$Proxy = & $PSScriptRoot\New-SSRSWebServiceProxy.ps1 -Uri $ServerUrl -Credential $Credential
New-SSRSFolder -Proxy $Proxy -Name $Folder
New-SSRSFolder -Proxy $Proxy -Name $DataSourceFolder
$DataSourcePaths = @{}
$Project.SelectNodes('Project/DataSources/ProjectItem') |
ForEach-Object {
$RdsPath = $ProjectRoot | Join-Path -ChildPath $_.FullPath
$DataSource = New-SSRSDataSource -Proxy $Proxy -RdsPath $RdsPath -Folder $DataSourceFolder
$DataSourcePaths.Add($DataSource.Name, $DataSource.Path)
}
$Project.SelectNodes('Project/Reports/ProjectItem') |
ForEach-Object {
$RdlPath = $ProjectRoot | Join-Path -ChildPath $_.FullPath
[xml]$Definition = Get-Content -Path $RdlPath
$NsMgr = New-XmlNamespaceManager $Definition d
$RawDefinition = Get-Content -Encoding Byte -Path $RdlPath
$Name = $_.Name -replace '\.rdl$',''
Write-Verbose "Creating report $Name"
$Results = $Proxy.CreateReport($Name, $Folder, $true, $RawDefinition, $null)
if ($Results -and ($Results | Where-Object { $_.Severity -eq 'Error' })) {
throw 'Error uploading report'
}
$Xpath = 'd:Report/d:DataSources/d:DataSource/d:DataSourceReference/..'
$DataSources = $Definition.SelectNodes($Xpath, $NsMgr) |
ForEach-Object {
$DataSourcePath = $DataSourcePaths[$_.DataSourceReference]
if (-not $DataSourcePath) {
throw "Invalid data source reference '$($_.DataSourceReference)' in $RdlPath"
}
$Reference = New-Object -TypeName SSRS.ReportingService2005.DataSourceReference
$Reference.Reference = $DataSourcePath
$DataSource = New-Object -TypeName SSRS.ReportingService2005.DataSource
$DataSource.Item = $Reference
$DataSource.Name = $_.Name
$DataSource
}
if ($DataSources) {
$Proxy.SetItemDataSources($Folder + '/' + $Name, $DataSources)
}
}
#requires -version 2.0
[CmdletBinding()]
param (
[parameter(Mandatory=$true)]
[ValidatePattern('\.rptproj$')]
[ValidateScript({ Test-Path -PathType Leaf -Path $_ })]
[string]
$Path,
[parameter(Mandatory=$true)]
[string]
$Configuration
)
function Normalize-SSRSFolder (
[string]$Folder
) {
if (-not $Folder.StartsWith('/')) {
$Folder = '/' + $Folder
}
return $Folder
}
$script:ErrorActionPreference = 'Stop'
Set-StrictMode -Version Latest
Write-Verbose "$($MyInvocation.MyCommand.Name) -Path $Path -Configuration $Configuration"
[xml]$Project = Get-Content -Path $Path
$Config = $Project.SelectNodes('Project/Configurations/Configuration') |
Where-Object { $_.Name -eq $Configuration } |
Select-Object -First 1
if (-not $Config) {
throw "Could not find configuration $Configuration."
}
$OverwriteDataSources = $false
if ($Config.Options.SelectSingleNode('OverwriteDataSources')) {
$OverwriteDataSources = [Convert]::ToBoolean($Config.Options.OverwriteDataSources)
}
return New-Object -TypeName PSObject -Property @{
ServerUrl = $Config.Options.TargetServerUrl
Folder = Normalize-SSRSFolder -Folder $Config.Options.TargetFolder
DataSourceFolder = Normalize-SSRSFolder -Folder $Config.Options.TargetDataSourceFolder
OverwriteDataSources = $OverwriteDataSources
}
#requires -version 2.0
[CmdletBinding()]
param (
[parameter(Mandatory=$true)]
[ValidatePattern('^https?://')]
[string]
$Uri,
[System.Management.Automation.PSCredential]
$Credential
)
$script:ErrorActionPreference = 'Stop'
Set-StrictMode -Version Latest
if (-not $Uri.EndsWith('.asmx')) {
if (-not $Uri.EndsWith('/')) {
$Uri += '/'
}
$Uri += 'ReportService2005.asmx'
}
$Assembly = [AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object {
$_.GetType('SSRS.ReportingService2005.ReportingService2005')
}
if (($Assembly | Measure-Object).Count -gt 1) {
throw 'AppDomain contains multiple definitions of the same type. Restart PowerShell host.'
}
if (-not $Assembly) {
if ($Credential) {
$CredParams = @{ Credential = $Credential }
} else {
$CredParams = @{ UseDefaultCredential = $true }
}
$Proxy = New-WebServiceProxy -Uri $Uri -Namespace SSRS.ReportingService2005 @CredParams
} else {
$Proxy = New-Object -TypeName SSRS.ReportingService2005.ReportingService2005
if ($Credential) {
$Proxy.Credentials = $Credential.GetNetworkCredential()
} else {
$Proxy.UseDefaultCredentials = $true
}
}
$Proxy.Url = $Uri
return $Proxy
@kulmam92

This comment has been minimized.

Copy link

commented Jun 4, 2013

Thanks for sharing good script. Would it be possible to add usage? Do you have any plan to upgrade it for SQL 2012?

@ChrisMissal

This comment has been minimized.

Copy link

commented Jul 11, 2013

Hey @kulmam92 and @codeassassin, here's my version which includes two different ways to call it: https://gist.github.com/ChrisMissal/5979564

@AlexBrown9

This comment has been minimized.

Copy link

commented Jan 17, 2014

This is pretty good script. Thanks for sharing.

I think the only thing it lacks is, functionality to deploy Shared Datasets, Is it possible to achieve this via Powershell Script?

@Jonesie

This comment has been minimized.

Copy link

commented Feb 15, 2014

Support for shared datasets and 2012. https://gist.github.com/Jonesie/9005796

@IgnEriol

This comment has been minimized.

Copy link

commented Feb 2, 2015

Hello jstangroome!

Starting from SSRS version 2008 R2 and higher method CreateDataSource returns CatalogItem. Today i've stucked with a problem caused by this, because after invoking $Proxy.CreateDataSource i received duplicated Name and Path from output of New-SSRSDataSource. First entry of this pair was returned by CreateDataSource and second by:
$DataSource = New-Object -TypeName PSObject -Property @{
Name = $Rds.RptDataSource.Name
Path = $Folder + '/' + $Rds.RptDataSource.Name
}. It is not a bug, but more behavior of PowerShell - it concatenated outputs (if they exist).
I've added Out-Null to line 112 and all is Ok now.

Thats why reports with embedded data sources did not deployed.

Thanks a lot for your solution.

@timabell

This comment has been minimized.

Copy link

commented Mar 27, 2015

Hi, did you write this script yourself? If so are you able to give permission for re-use? Maybe under MIT or something.

@timabell

This comment has been minimized.

Copy link

commented Mar 30, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.