Scenarios: Deploy SSRS reports using automation
#Set variables with configure values
$Environment = "DEV"
$reportPath ="/"
$reportFolder = "DemoReports"
$SourceDirectory = "E:\Test" # local folder
path where actual files exists
$DataSourcePath = "/"
$DataSet_Folder = "/"
$IsOverwriteDataSource =1
$IsOverwriteDataSet =1
$IsOverwriteReport =1
# Set server IP address based on Environment provided from
Config file
IF( $Environment
-eq "DEV")
{
$webServiceUrl
= "http://server"
# or http://xx.xxx.x..xxx
}
ELSEIF ( $Environment -eq
"TEST")
{
$webServiceUrl
= "http://server"
# or http://xx.xxx.x..xxx
}
ELSEIF ($Environment -eq
"PROD")
{
$webServiceUrl
= "http://server"
# or http://xx.xxx.x..xxx
}
#Connecting to SSRS
Write-Host "Reportserver:
$webServiceUrl" -ForegroundColor
Magenta
Write-Host "Creating
Proxy, connecting to : $webServiceUrl/ReportServer/ReportService2010.asmx?WSDL"
Write-Host ""
$ssrsProxy = New-WebServiceProxy
-Uri $webServiceUrl'/ReportServer/ReportService2010.asmx?WSDL' -UseDefaultCredential
$reportFolder_Final = $reportPath
+ $reportFolder
##########################################
#Create Report Folder
Write-host
""
try
{
$ssrsProxy.CreateFolder($reportFolder, $reportPath, $null)
Write-Host
"Created new folder: $reportFolder_Final"
}
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
}
}
##########################################
#Create datasource
foreach($rdsfile
in Get-ChildItem
$SourceDirectory -Filter
*.rds)
{
Write-host $rdsfile
#create data source
try
{
$rdsf
= [System.IO.Path]::GetFileNameWithoutExtension($rdsfile);
$RdsPath
= $SourceDirectory+"\"+$rdsf+".rds"
Write-host
"Reading data from $RdsPath"
[xml]$Rds = Get-Content -Path $RdsPath
$ConnProps
= $Rds.RptDataSource.ConnectionProperties
$type
= $ssrsProxy.GetType().Namespace
$datatype
= ($type
+ '.DataSourceDefinition')
$datatype_Prop
= ($type
+ '.Property')
$DescProp
= New-Object($datatype_Prop)
$DescProp.Name = 'Description'
$DescProp.Value = ''
$HiddenProp
= New-Object($datatype_Prop)
$HiddenProp.Name = 'Hidden'
$HiddenProp.Value = 'false'
$Properties
= @($DescProp, $HiddenProp)
$Definition
= New-Object
($datatype)
$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
($IsOverwriteDataSource -eq 1)
{
[boolean]$IsOverwriteDataSource =
1
}
else
{
[boolean]$IsOverwriteDataSource =
0
}
$warnings
= $ssrsProxy.CreateDataSource($rdsf, $reportFolder_Final
,$IsOverwriteDataSource, $Definition, $Properties)
# Write-Host $warnings
}
catch [System.IO.IOException]
{
$msg
= "Error
while reading rds file : '{0}', Message: '{1}'" -f $rdsfile, $_.Exception.Message
Write-Error
msgcler
}
catch [System.Web.Services.Protocols.SoapException]
{
if
($_.Exception.Detail.InnerText
-match "[^rsItemAlreadyExists400]")
{
Write-Host
"DataSource: $rdsf already exists."
}
else
{
$msg
= "Error
uploading report: $rdsf. Msg: '{0}'" -f
$_.Exception.Detail.InnerText
Write-Error
$msg
}
}
}
##########################################
# Create Dataset
Write-host "dataset changes start"
foreach($rsdfile in Get-ChildItem $SourceDirectory
-Filter *.rsd)
{
Write-host ""
$rsdf = [System.IO.Path]::GetFileNameWithoutExtension($rsdfile)
$RsdPath
= $SourceDirectory+'\'+$rsdf+'.rsd'
Write-Verbose
"New-SSRSDataSet -RsdPath $RsdPath -Folder $DataSet_Folder"
$RawDefinition
= Get-Content
-Encoding Byte
-Path $RsdPath
$warnings
= $null
$Results = $ssrsProxy.CreateCatalogItem("DataSet", $rsdf, $reportFolder_Final, $IsOverwriteDataSet, $RawDefinition, $null, [ref]$warnings)
write-host "dataset created successfully"
}
#############################
#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
Write-Host
"Uploading to: $reportFolder_Final"
$type
= $ssrsProxy.GetType().Namespace
$datatype
= ($type
+ '.Property')
$DescProp
= New-Object($datatype)
$DescProp.Name = 'Description'
$DescProp.Value = ''
$HiddenProp
= New-Object($datatype)
$HiddenProp.Name = 'Hidden'
$HiddenProp.Value = 'false'
$Properties
= @($DescProp, $HiddenProp)
#Call
Proxy to upload report
$warnings
= $null
$Results
= $ssrsProxy.CreateCatalogItem("Report", $reportName,$reportFolder_Final, $IsOverwriteReport,$byteArray,$Properties,[ref]$warnings)
if($warnings.length
-le 1)
{ Write-Host
"Upload Success." -ForegroundColor Green
}
else
{ write-host
$warnings
}
}
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]
{
$msg
= "Error
uploading report: $reportName. Msg: '{0}'" -f
$_.Exception.Detail.InnerText
Write-Error
$msg
}
##########################################
##Change
Datasource
$reportFullName
= $reportFolder_Final+"/"+$reportName
Write "datasource record $reportFullName"
$rep = $ssrsProxy.GetItemDataSources($reportFullName)
$rep | ForEach-Object
{
$proxyNamespace
= $_.GetType().Namespace
$constDatasource
= New-Object
("$proxyNamespace.DataSource")
$constDatasource.Item = New-Object ("$proxyNamespace.DataSourceReference")
$FinalDatasourcePath
=$reportPath+$reportFolder+"/"+ $($_.Name)
$constDatasource.Item.Reference
= $FinalDatasourcePath
$_.item = $constDatasource.Item
$ssrsProxy.SetItemDataSources($reportFullName, $_)
Write-Host "Changing datasource `"$($_.Name)`" to $($_.Item.Reference)"
}
}
Write-host ""
Write-host "Successfully
completed SSRS deployment" -ForegroundColor
Magenta
Write-host ""
***
Script can be downloaded from beow MSDN link https://social.technet.microsoft.com/wiki/contents/articles/34521.powershell-script-for-ssrs-project-deployment.aspx
**********************
Keywords:
ssrs automation , ssrs deployment automation, ssrs deploy powershell,ssrs deployment powershell
powershell ssrs deployment,powershell ssrs report,ssrs powershell cmdlets,ssrs powershell deploy,
ssrs powershell create folder, PowerShell Script Deploying SSRS Report
