Skip to content

Instantly share code, notes, and snippets.

@jstangroome
Created July 3, 2012 22:37
Show Gist options
  • Save jstangroome/3043878 to your computer and use it in GitHub Desktop.
Save jstangroome/3043878 to your computer and use it in GitHub Desktop.
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
Copy link

kulmam92 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
Copy link

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

@AlexBrown9
Copy link

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
Copy link

Jonesie commented Feb 15, 2014

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

@IgnEriol
Copy link

IgnEriol 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
Copy link

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

@timabell
Copy link

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment