Forum Discussion
Powershell script to change Datasource Test to Production
(Responding to fbeekvel)
Everything I've tried to change the ReportServer connection string, including all 3 articles you've attached. The connection string is local to the file, and the file only. The connection string is read-only in the ReportServer.
I'd love to be proven wrong, but I don't think it's possible. One possible way is to zip the file and change the connection string text, then zip it back up. That's not enterprise-grade though so I haven't pursued it.
- I'm pretty sure It works. I have a working script somewhere, I'll see if I can post it tomorrow.
#Install-Module -Name ReportingServicesTools
<# Set parameters #>
$ReportServerURI = 'https://MyReportServer/reports'
$MyReport = "/Folder/Reportname"
$MyUserName = "domain\account"
$MyPassword = "password"try {
Write-Verbose "Creating a session to the Report Server $ReportServerURI"
# establish session w/ Report Server
$session = New-RsRestSession -ReportPortalUri $ReportServerURI
Write-Verbose "Created a session to the Report Server $ReportServerURI"
}
catch {
Write-Warning "Failed to create a session to the report server $ReportServerURI"
Return
}
try {
Write-Verbose "Getting the datasources from the pbix file for updating"
# get data source object
$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$MyReport"
Write-Verbose "Got the datasources for updating"
}
catch {
Write-Warning "Failed to get the datasources"
Return
}
try {
Write-Verbose "Updating Datasource"
foreach ($dataSource in $datasources) {
$dataSource.CredentialRetrieval = 'Store'
$dataSource.ConnectionString = "Data Source=SSASserver;Initial Catalog=SSASdatabase"
$dataSource.IsConnectionStringOverridden = $True
$dataSource.DataModelDataSource.Username = $MyUserName
$dataSource.DataModelDataSource.Secret = $MyPassword
}
Write-Verbose "Updating the data source for the report $PBIXName"
# update data source object on server
Set-RsRestItemDataSource -WebSession $session -RsItem "$MyReport" -RsItemType PowerBIReport -DataSources $datasource
}
catch {
Write-Warning "Failed to set the datasource"
Return
}
Write-Verbose "Completed Successfully"this one is for updating a connectionstring to a SSAS database using stored credentials.