Opening a SSRS project using Powershell

Question

I have a report that is copied to a number of different servers. It is imported manually and the data source properties are altered to match the current server's specs. I would like to be able to automate the process by enabling users to open a the SSRS report and dynamically alter it's shared data source properties through PowerShell. I hope you could help. You may see reference below.

The script would accept an input parameter for servername, username and password. Also, the save my password must be ticked.

Answer 1

I couldn't believe I managed to create a script for this. You may make use of the script below as future reference. Comments are available for each part and anything that needs to be altered has a "here" keyword , ex. Your_database_name_here .

Import-Module SqlPs

#Input parameter to get Server\Instancename of your Datasource
$Servername = Read-Host "Please enter your Servername"
$Instancename = Read-Host "Please enter your Instancename. For default instance please press enter"
Write-host ""

if ($Instancename -eq ""){
   $ServerInstance = $Servername
   }
Else {
   $ServerInstance = $Servername +"\"+ $InstanceName
}

#Setting up SSRS Target URL. This is the location where your reports would be deployed.
if ($Instancename -eq ""){
   $ReportServerUri = "http://$Servername/ReportServer//ReportService2010.asmx?wsdl"
   $TargetURL = "http://$Servername/Reports"
   }
Else {
   $ReportServerUri = "http://$Servername/ReportServer_$Instancename//ReportService2010.asmx?wsdl"
   $TargetURL = "http://$Servername/Reports_$Instancename"
}

$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCreden



#We would make use of SQL Server Authentication for the reports shared datasource so you need to supply a username and password.
Write-Host "     SQL Server Authentication:"
$Username = Read-Host "     Username"
$Password = Read-Host -AsSecureString "Password" 


$type = $Proxy.GetType().Namespace
$datatype = ($type + '.Property')

$property =New-Object ($datatype);
$property.Name = “NewFolder”
$property.Value = “NewFolder”

$numproperties = 1
$properties = New-Object ($datatype + '[]')$numproperties 
$properties[0] = $property;

$newFolder = $proxy.CreateFolder("Reports”, “/”, $properties);
$newFolder = $proxy.CreateFolder("Data Sources”, “/”, $properties);

$Children =$proxy.ListChildren("/",$false)
$DBname = 'Your_Database_Name_Here'


# Creating Datasource through powershell
Write-Host "     Creating Datasource ..."
$Name = "Name_Your_Datasource_here"
$Parent = "/Data Sources"
$ConnectString = "data source=$Servername\$Instancename;initial catalog=$DBname"
$type = $Proxy.GetType().Namespace
$DSDdatatype = ($type + '.DataSourceDefinition')
$DSD = new-object ($DSDdatatype)
if($DSD -eq $null){
      Write-Error Failed to create data source definition object
}
$CredentialDataType = ($type + '.CredentialRetrievalEnum')
$Cred = new-object ($CredentialDataType)
$CredEnum = ($CredentialDataType).Integrated
$Cred.value__=1 

$DSD.CredentialRetrieval =$Cred
$DSD.ConnectString = $ConnectString
$DSD.Enabled = $true
$DSD.EnabledSpecified = $false
$DSD.Extension = "SQL"
$DSD.ImpersonateUserSpecified = $false
$DSD.Prompt = $null
$DSD.WindowsCredentials = $false
$DSD.UserName = $Username
$DSD.Password = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password))

$newDSD = $proxy.CreateDataSource($Name,$Parent,$true,$DSD,$null)

#Deploying RLD files to Target URL
Write-Host "     Deploying RDL files ..."
$stream = Get-Content 'D:\Your_RDL_path_here.rdl' -Encoding byte
$warnings =@();
$proxy.CreateCatalogItem("Report","Report_Name_here","/Reports",$true,$stream,$null,[ref]$warnings)


#Let's make use of the datasource we just created for your RDL files.
$Items = $global:proxy.listchildren("/Data Sources", $true) 
foreach ($item in $items)
{

$DatasourceName = $item.Name
$DatasourcePath = $item.Path
}


$RDLS = $global:proxy.listchildren("/Reports", $true) 
foreach ($rdl in $rdls)
{
$report = $rdl.path


$rep = $global:proxy.GetItemDataSources($report)
$rep | ForEach-Object {
$proxyNamespace = $_.GetType().Namespace
    $constDatasource = New-Object ("$proxyNamespace.DataSource")
    $constDatasource.Name = $DataSourceName
    $constDatasource.Item = New-Object ("$proxyNamespace.DataSourceReference")
    $constDatasource.Item.Reference = $DataSourcePath

$_.item = $constDatasource.Item
$global:proxy.SetItemDataSources($report, $_)
Write-Host "Changing datasource `"$($_.Name)`" to $($_.Item.Reference)"
}
}

#Open a IE browser to view the report.
$IE=new-object -com internetexplorer.application
$IE.navigate2($TargetURL)
$IE.visible=$true
Write-Host ""     
Write-Host "You may now view the Reports through the open IE browser."
Write-Host -ForegroundColor Green "**STEP COMPLETED!"