Last active
June 23, 2020 22:03
-
-
Save SQLvariant/f9b45ecea9ea31d1f9e597c577e63fe4 to your computer and use it in GitHub Desktop.
I want to get the 'Target...' properties from my deployment configuration.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function Deploy-RsProject | |
{ | |
<# | |
.SYNOPSIS | |
This script deploys a Reporting Services project to a Power BI Report Server. | |
.DESCRIPTION | |
This function deploys a full SSRS project to a Power BI Report Server. | |
.PARAMETER ProjectFile | |
Specify the location of the SSRS project file whose deployment profiles should be fetched. | |
.EXAMPLE | |
Deploy-RsProject -ProjectFile 'C:\Users\Aaron\source\repos\Finance\Financial Reports\SSRS_FR\SSRS_FR.rptproj' | |
Description | |
----------- | |
Deploys all project files using all applicable settings from the project file. | |
#> | |
[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')] | |
param ( | |
[Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)] | |
[string]$RsProjectFile, | |
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)] | |
[string]$TargetServerURL, | |
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)] | |
[string]$TargetReportFolder, | |
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)] | |
[string]$TargetDatasourceFolder, | |
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)] | |
[string]$TargetDatasetFolder, | |
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)] | |
[string]$TargetReportPartFolder, | |
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)] | |
[string]$OverwriteDatasets, | |
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)] | |
[string]$OverwriteDataSources, | |
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)] | |
[string]$FullPath, | |
[Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)] | |
[string]$ReportPortal | |
) | |
[string]$ProjectFolder=Split-Path -Path $RsProjectFile | |
"`$ProjectFolder = '$ProjectFolder'" | |
"This deployment is going to happen using the $ConfigurationToUse profile. | |
" | |
$RSConfig | FL | |
<# RsFolder Structure | |
Make sure all the folders needed already exist with the following code. #> | |
Write-Host " | |
Beginning deployment. | |
Building folder structures... | |
" | |
$TargetReportFolder, $TargetDatasourceFolder, $TargetDatasetFolder | | |
sort -Unique | | |
foreach { | |
MakeDeploymentFolders -RsFolder $_ -ReportPortal $ReportPortal | |
} | |
<# Deploy Data Sources #> | |
Write-Host " | |
Deploying Data Sources to $($TargetDatasourceFolder)... | |
" | |
foreach($RDS in dir -Path $ProjectFolder -Filter *.rds) | |
{ | |
try{ Write-Verbose "Checking for $TargetDatasourceFolder/$($_.BaseName)" | |
Get-RsRestItem -ReportPortalUri $ReportPortal -RsItem "$TargetDatasourceFolder/$($RDS.BaseName)" | ft -AutoSize | |
} | |
catch{ Write-Verbose 'Did not find Data Source' | |
Write-RsRestCatalogItem -Path "$ProjectFolder\$($RDS.Name)" -ReportPortalUri $ReportPortal -RsFolder $TargetDatasourceFolder | |
} | |
} | |
<# Deploy Data Sets & set their Data Source References. #> | |
Write-Host " | |
Deploying DataSets to $TargetDatasetFolder... | |
" | |
dir -Path $ProjectFolder -Filter *.rsd | | |
foreach{ | |
[XML]$dsetref = Get-Content "$ProjectFolder\$($_.Name)" | |
$DataSetQuery = $dsetref.SharedDataSet.DataSet.Query | |
$DSetConfig = [pscustomobject]@{ | |
DataSourceReference = $dsetref.SharedDataSet.DataSet.Query.DataSourceReference | |
CommandText = $dsetref.SharedDataSet.DataSet.Query.CommandText | |
CommandType = $dsetref.SharedDataSet.DataSet.Query.CommandType | |
DataSetParameters = $dsetref.SharedDataSet.DataSet.Query.DataSetParameters | |
} | |
Write-RsRestCatalogItem -Path "$ProjectFolder\$($_.Name)" -ReportPortalUri $ReportPortal -RsFolder $TargetDatasetFolder -Overwrite | |
Set-RsDataSourceReference -ReportServerUri $ReportServer -Path "$TargetDatasetFolder/$($_.BaseName)" -DataSourceName DataSetDataSource -DataSourcePath "$($TargetDatasourceFolder)/$($DSetConfig.DataSourceReference)" | |
} | |
<# Deploy the Reports #> | |
Write-Host "Deploying the report files to $TargetReportFolder... | |
" | |
dir -Path $ProjectFolder -Filter *.rdl | | |
foreach{ | |
$ReportName=$_.BaseName | |
Write-RsCatalogItem -Path "$ProjectFolder\$($_.Name)" -ReportServerUri $ReportServer -RsFolder $TargetReportFolder -Overwrite | |
"$($_.BaseName)"; | |
Get-RsRestItemDataSource -ReportPortalUri $ReportPortal -RsItem "$TargetReportFolder/$ReportName" | | |
foreach{ | |
Set-RsDataSourceReference -ReportServerUri $ReportServer -Path "$TargetReportFolder/$ReportName" -DataSourceName $_.Name -DataSourcePath "$($TargetDatasourceFolder)/$($_.Name)" | |
} | |
} | |
<# Now read in the DataSet References directly from the report files and set them on the server #> | |
if($TargetDatasetFolder -ne $TargetReportFolder){ | |
$Reports = dir -Path $ProjectFolder -Filter *.rdl | |
foreach($Report in $Reports) | |
{ | |
[XML]$ReportDSetRef = Get-Content $Report.FullName | |
foreach($SDS in $ReportDSetRef.Report.DataSets.DataSet){ | |
Set-RsDataSetReference -ReportServerUri $ReportServer -Path "$TargetReportFolder/$($Report.BaseName)" -DataSetName $SDS.Name -DataSetPath "$TargetDatasetFolder/$($SDS.SharedDataSet.SharedDataSetReference)" | |
} | |
} | |
} | |
}#-End of function |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function Get-RsDeploymentConfig { | |
param ( | |
[Parameter(Mandatory = $true)] | |
[string]$RsProjectFile, | |
[Parameter(Mandatory = $false)] | |
[string]$ConfigurationToUse | |
) | |
if($ConfigurationToUse){ | |
[XML]$rptproj = Get-Content $RsProjectFile | |
$Deployment = $rptproj.Project.PropertyGroup | where { $_.FullPath -eq $ConfigurationToUse } | |
$RSConfig = [pscustomobject]@{ | |
FullPath = $Deployment.FullPath | |
OverwriteDatasets = $Deployment.OverwriteDatasets | |
OverwriteDataSources = $Deployment.OverwriteDataSources | |
TargetReportFolder = (StripTrailingSlash $Deployment.TargetReportFolder) | |
TargetDatasetFolder = (StripTrailingSlash $Deployment.TargetDatasetFolder) | |
TargetDatasourceFolder = (StripTrailingSlash $Deployment.TargetDatasourceFolder) | |
TargetReportPartFolder = (StripTrailingSlash $Deployment.TargetReportPartFolder) | |
TargetServerURL = $Deployment.TargetServerURL | |
RsProjectFile = $RsProjectFile | |
} | |
return $RSConfig | |
} | |
else{[XML]$rptproj = Get-Content $RsProjectFile | |
$ConfigurationToUse = $rptproj.Project.PropertyGroup.FullPath | ogv -PassThru | |
$Deployment = $rptproj.Project.PropertyGroup | where { $_.FullPath -eq $ConfigurationToUse } | |
$RSConfig = [pscustomobject]@{ | |
FullPath = $Deployment.FullPath | |
OverwriteDatasets = $Deployment.OverwriteDatasets | |
OverwriteDataSources = $Deployment.OverwriteDataSources | |
TargetReportFolder = (StripTrailingSlash $Deployment.TargetReportFolder) | |
TargetDatasetFolder = (StripTrailingSlash $Deployment.TargetDatasetFolder) | |
TargetDatasourceFolder = (StripTrailingSlash $Deployment.TargetDatasourceFolder) | |
TargetReportPartFolder = (StripTrailingSlash $Deployment.TargetReportPartFolder) | |
TargetServerURL = $Deployment.TargetServerURL | |
RsProjectFile = $RsProjectFile | |
} | |
return $RSConfig | |
} | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function MakeDeploymentFolders { | |
param($RsFolder,$ReportPortal) | |
$tree=$null | |
$tree | |
$Base='/' | |
($RsFolder.substring(1,$RsFolder.length-1)).split('/') | foreach{ | |
$Folder = $_ | |
$tree += "/"+$Folder | |
try{ | |
Get-RsRestItem -ReportPortalUri $ReportPortal -RsItem $tree| ft -AutoSize | |
} | |
catch{ | |
Write-Warning "Folder $tree does not exist"; | |
New-RsRestFolder -ReportPortalUri $ReportPortal -RsFolder $Base -FolderName $Folder -Verbose | |
} | |
$Base=$tree | |
} | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[XML]$rptproj = Get-Content "c:\SQL Server Performance Dashboard\SQL Server Performance Dashboard\SQL Server Performance Dashboard.rptproj" | |
$Deployment = $rptproj.Project.Configurations.Configuration | where {$_.Name -eq 'Debug'} | | |
$Deployment.Options | |
$RSConfig = [pscustomobject]@{ | |
TargetServerVersion = $Deployment.Options.TargetServerVersion | |
TargetServerURL = $Deployment.Options.TargetServerURL | |
TargetFolder = $Deployment.Options.TargetFolder | |
TargetDataSourceFolder = $Deployment.Options.TargetDataSourceFolder | |
TargetDatasetFolder = $Deployment.Options.TargetDatasetFolder | |
TargetReportPartFolder = $Deployment.Options.TargetReportPartFolder | |
} | |
$RSConfig | FT -AutoSize |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?xml version="1.0" encoding="Windows-1252"?> | |
<Project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ToolsVersion="2.0"> | |
<State>$base64$PFNvdXJjZUNvbnRyb2xJbmZvIHhtbG5zOnhzZD0iaHR0cDovL3d3dy53My5vcmcvMjAwMS9YTUxTY2hlbWEiIHhtbG5zOnhzaT0iaHR0cDovL3d3dy53My5vcmcvMjAwMS9YTUxTY2hlbWEtaW5zdGFuY2UiIHhtbG5zOmRkbDI9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vYW5hbHlzaXNzZXJ2aWNlcy8yMDAzL2VuZ2luZS8yIiB4bWxuczpkZGwyXzI9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vYW5hbHlzaXNzZXJ2aWNlcy8yMDAzL2VuZ2luZS8yLzIiIHhtbG5zOmRkbDEwMF8xMDA9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vYW5hbHlzaXNzZXJ2aWNlcy8yMDA4L2VuZ2luZS8xMDAvMTAwIiB4bWxuczpkZGwyMDA9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vYW5hbHlzaXNzZXJ2aWNlcy8yMDEwL2VuZ2luZS8yMDAiIHhtbG5zOmRkbDIwMF8yMDA9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vYW5hbHlzaXNzZXJ2aWNlcy8yMDEwL2VuZ2luZS8yMDAvMjAwIiB4bWxuczpkZGwzMDA9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vYW5hbHlzaXNzZXJ2aWNlcy8yMDExL2VuZ2luZS8zMDAiIHhtbG5zOmRkbDMwMF8zMDA9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vYW5hbHlzaXNzZXJ2aWNlcy8yMDExL2VuZ2luZS8zMDAvMzAwIiB4bWxuczpkZGw0MDA9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vYW5hbHlzaXNzZXJ2aWNlcy8yMDEyL2VuZ2luZS80MDAiIHhtbG5zOmRkbDQwMF80MDA9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vYW5hbHlzaXNzZXJ2aWNlcy8yMDEyL2VuZ2luZS80MDAvNDAwIiB4bWxuczpkd2Q9Imh0dHA6Ly9zY2hlbWFzLm1pY3Jvc29mdC5jb20vRGF0YVdhcmVob3VzZS9EZXNpZ25lci8xLjAiPg0KICA8RW5hYmxlZD5mYWxzZTwvRW5hYmxlZD4NCiAgPFByb2plY3ROYW1lPjwvUHJvamVjdE5hbWU+DQogIDxBdXhQYXRoPjwvQXV4UGF0aD4NCiAgPExvY2FsUGF0aD48L0xvY2FsUGF0aD4NCiAgPFByb3ZpZGVyPjwvUHJvdmlkZXI+DQo8L1NvdXJjZUNvbnRyb2xJbmZvPg==</State> | |
<DataSources /> | |
<DataSets /> | |
<Reports> | |
<ProjectItem> | |
<Name>database_overview.rdl</Name> | |
<FullPath>database_overview.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>database_storage_report.rdl</Name> | |
<FullPath>database_storage_report.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>historical_io.rdl</Name> | |
<FullPath>historical_io.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>historical_waits.rdl</Name> | |
<FullPath>historical_waits.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>missing_index_from_showplan.rdl</Name> | |
<FullPath>missing_index_from_showplan.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>missing_indexes.rdl</Name> | |
<FullPath>missing_indexes.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>page_details.rdl</Name> | |
<FullPath>page_details.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>performance_dashboard_main.rdl</Name> | |
<FullPath>performance_dashboard_main.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>plan_guide.rdl</Name> | |
<FullPath>plan_guide.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>query_plan.rdl</Name> | |
<FullPath>query_plan.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>query_stats.rdl</Name> | |
<FullPath>query_stats.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>query_stats_details.rdl</Name> | |
<FullPath>query_stats_details.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>recent_cpu.rdl</Name> | |
<FullPath>recent_cpu.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>requests_overview.rdl</Name> | |
<FullPath>requests_overview.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>session_details.rdl</Name> | |
<FullPath>session_details.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>sessions_overview.rdl</Name> | |
<FullPath>sessions_overview.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>traces.rdl</Name> | |
<FullPath>traces.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>wait_blocking.rdl</Name> | |
<FullPath>wait_blocking.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>wait_buffer_io.rdl</Name> | |
<FullPath>wait_buffer_io.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>wait_buflatch.rdl</Name> | |
<FullPath>wait_buflatch.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>wait_generic.rdl</Name> | |
<FullPath>wait_generic.rdl</FullPath> | |
</ProjectItem> | |
<ProjectItem> | |
<Name>wait_latch.rdl</Name> | |
<FullPath>wait_latch.rdl</FullPath> | |
</ProjectItem> | |
</Reports> | |
<Configurations> | |
<Configuration> | |
<Name>Debug</Name> | |
<Platform>Win32</Platform> | |
<Options> | |
<OutputPath>bin\Debug</OutputPath> | |
<TargetServerVersion>SSRS2008R2</TargetServerVersion> | |
<TargetServerURL>http://pariklaptop/reportserver</TargetServerURL> | |
<TargetFolder>SQL Server Performance Dashboard</TargetFolder> | |
<TargetDataSourceFolder>Data Sources</TargetDataSourceFolder> | |
<TargetDatasetFolder>Datasets</TargetDatasetFolder> | |
<TargetReportPartFolder>Report Parts</TargetReportPartFolder> | |
</Options> | |
</Configuration> | |
<Configuration> | |
<Name>DebugLocal</Name> | |
<Platform>Win32</Platform> | |
<Options> | |
<OutputPath>bin\DebugLocal</OutputPath> | |
<TargetServerVersion>SSRS2008R2</TargetServerVersion> | |
<TargetFolder>SQL Server Performance Dashboard</TargetFolder> | |
<TargetDataSourceFolder>Data Sources</TargetDataSourceFolder> | |
<TargetDatasetFolder>Datasets</TargetDatasetFolder> | |
<TargetReportPartFolder>Report Parts</TargetReportPartFolder> | |
</Options> | |
</Configuration> | |
<Configuration> | |
<Name>Release</Name> | |
<Platform>Win32</Platform> | |
<Options> | |
<OutputPath>bin\Release</OutputPath> | |
<TargetServerVersion>SSRS2008R2</TargetServerVersion> | |
<TargetFolder>SQL Server Performance Dashboard</TargetFolder> | |
<TargetDataSourceFolder>Data Sources</TargetDataSourceFolder> | |
<TargetDatasetFolder>Datasets</TargetDatasetFolder> | |
<TargetReportPartFolder>Report Parts</TargetReportPartFolder> | |
</Options> | |
</Configuration> | |
</Configurations> | |
</Project> |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function StripTrailingSlash { | |
param($t) | |
if($t.endswith("/")) { | |
$t.substring(0,$t.length-1) | |
} else { | |
$t | |
} | |
} |
Oh Doug, I should have mentioned. I'm actually trying to make the experience more like this:
$rptproj.Project.Configurations.Configuration | OGV -PassThru | SELECT -ExpandProperty Options
Example call for Get-RsDeploymentConfig:
$RSConfig = Get-RsDeploymentConfig -RsProjectFile 'C:\Users\Aaron\source\repos\Finance\SSRS_Finance\SSRS_Finance.rptproj' -ConfigurationToUse Dev01 $RSConfig | Add-Member -PassThru -MemberType NoteProperty -Name ReportPortal -Value 'http://localhost/PBIRSportal/' $RSConfig | Deploy-RsProject
Second example:
$RSConfig = Get-RsDeploymentConfig -RsProjectFile 'C:\Users\Aaron\source\repos\Finance\SSRS_Finance\SSRS_Finance.rptproj'
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
A Start
Result