PowerShell Script for SSRS PRoject Deployment

Article
1/17/2024

Scenario: Automate SSRS deployment

Script:

#Set variables with configure values
$Environment = "DEV" #specifiying Dev/Test/Prod Environment
$reportPath ="/" # Rool Level
$reportFolder = "DemoReports"
$SourceDirectory = "E:\Test" # Local drive where actual RDL/RDS/RSD files contains
$DataSourcePath = "/DemoReports" #Folder where we need to create DAtasource
$DataSet_Folder = "/DemoReports" # Folder where we need to create DataSet
$IsOverwriteDataSource =1
$IsOverwriteDataSet =1
$IsOverwriteReport =1

# Set server IP address based on Environment provided from Config file
IF( $Environment -eq "DEV")
{
$webServiceUrl = "http://Server" # you can also give like "Http://xxx.xxx.xx.xx"
}
ELSEIF ( $Environment -eq "TEST")
{
$webServiceUrl = "http://Server"
}
ELSEIF ($Environment -eq "PROD")
{
$webServiceUrl = "http://Server"
}

#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 = $DataSourcePath+"/" + $($_.Name)
$constDatasource.Item.Reference = $FinalDatasourcePath

$_.item = $constDatasource.Item
$ssrsProxy.SetItemDataSources($reportFullName, $_)
Write-Host "Changing datasource `"$($_.Name)`" to $($_.Item.Reference)"
}

}

Write-host ""
Write-host " We have successfully Deployed SSRS Project" -ForegroundColor Magenta
Write-host ""

Share via

PowerShell Script for SSRS PRoject Deployment

Additional resources