I'm quite in an impasse here. I started writing a script with PowerShell to handle setting Shared Datasources to all the reports of a folder.
My logic : For each report in the folder and For each datasource of the report :
If the datasource name matches with the one to be replace 1 : If its reference is already set up : replace it with the shared one 2 : If its a Custom Datasource : replace it with the shared one 3 : If its reference is empty : replace it with the shared one
Case 1 works perfectly, while Case 2 and Case 3 fail. I don't understand why for the first case it accept a single element while for the other, it fails telling me that it needs an array :
Impossible de convertir l'argument «DataSources» (valeur «SSRS.ReportingService2010.DataSource») de «SetItemDataSources
» en type «SSRS.ReportingService2010.DataSource[]»: «Impossible de convertir la valeur «
SSRS.ReportingService2010.DataSource» du type «SSRS.ReportingService2010.DataSource» en type «
SSRS.ReportingService2010.DataSource[]».»
Au caractère Ligne:87 : 1
+ $proxy.SetItemDataSources($ssrsItem.Path, $newDataSource);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument
Here's the entire code I came up with (its verbose but I think it helps readability) :
# Usage : SetConnection "msbireports" "/2134_DELIVRI/Test/PS1" "EDUCFI_SSAS" "/2134_DELIVRI/Test/PS1/Datasources/EDUCFI_SSAS.rds" true
function SetConnection(
$reportServerName = $(throw "reportServerName is required."),
$serverPath = $(throw "serverPath is required."),
$connectionName = $(throw "connectionName is required."),
$toConnection = $(throw "toConnection is required."),
$replaceCustom = $(throw "replaceCustom is required.")
)
{
Write-Host "> Connecting to $reportServerName" -ForegroundColor Yellow
$reportServerUri = "https://{0}/ReportServer/ReportService2010.asmx" -f $reportServerName
$proxy = New-WebServiceProxy -Uri $reportServerUri -Namespace SSRS.ReportingService2010 -UseDefaultCredential
Write-Host " Done !" -ForegroundColor Yellow
Write-Host ""
# List everything on the Report Server, not recursively, but filter to keep Reports
Write-Host "> Getting Items from $serverPath"
$items = $proxy.ListChildren($serverPath, $false) | Where-Object {$_.TypeName -eq "Report"}
Write-Host "Found $($items.Length) reports..."
Write-Host ""
# Loop through reports and data sources
Foreach($ssrsItem in $items)
{
# Handling report.rdl...
Write-Host "> Handling $($ssrsItem.Path).rdl"
# Get Datasources having the wanted name
$dsItems = $proxy.GetItemDataSources($ssrsItem.Path)
$dsTarget = $proxy.GetItemDataSources($ssrsItem.Path) | Where-Object {$_.Name -eq $connectionName}
if ($dsTarget.Length -eq 0)
{
Write-Host ">> Skipped : no DataSource named $connectionName" -ForegroundColor Gray
Write-Host ""
}
else
{
Foreach($dsItem in $dsItems)
{
if ($dsItem.Name -eq $connectionName)
{
# Check if the object has a reference or not (i.e. : its filled shared datasource or not : custom dataset OR empty filled shared datasource)
if($dsItem.Item.PSObject.Properties.Match('Reference').Count)
{
# Does the shared Datasource already points to the good one ?
if ($dsItem.Item.Reference -eq $toConnection)
{
Write-Host ">> Nothing done : $connectionName already set up as $toConnection" -ForegroundColor Gray
Write-Host ""
}
else
{
# Shared Datasource with an already filled Shared Datasource which points diffrently : change it.
Write-Host ">> Changing $connectionName from $($dsItem.Item.Reference) to $toConnection..." -ForegroundColor Gray
$dsItem.Item.Reference = $toConnection;
$proxy.SetItemDataSources($ssrsItem.Path, $dsItem);
Write-Host " Done!" -ForegroundColor Green
Write-Host ""
}
}
else
{
# Two cases here : 1. Custom Datasource or 2. SharedDatasource but empty or
# Check for Custom Datasource (has a property called Enabled which ShareDatasource has not)
if($dsItem.Item.PSObject.Properties.Match('Enabled').Count)
{
if ($replaceCustom -eq "replace")
{
Write-Host ">> Changing Custom Connection $connectionName to a Shared one pointing to $toConnection..." -ForegroundColor Gray
$newDataSource = New-Object("SSRS.ReportingService2010.DataSource")
$newDataSource.Name = $connectionName
$newDataSource.Item = New-Object ("SSRS.ReportingService2010.DataSourceReference")
$newDataSource.Item.Reference = $toConnection
$proxy.SetItemDataSources($ssrsItem.Path, $newDataSource);
Write-Host " DOES NOT WORK !" -ForegroundColor RED
Write-Host ""
}
else
{
Write-Host ">> Nothing done : $connectionName set up as a Custom Connection, but was told not to change it" -ForegroundColor Gray
Write-Host ""
}
}
else
{
Write-Host ">> OF TYPE SHARED... But not really ???"
$newDataSource = New-Object("SSRS.ReportingService2010.DataSource")
$newDataSource.Name = $connectionName
$newDataSource.Item = New-Object ("SSRS.ReportingService2010.DataSourceReference")
$newDataSource.Item.Reference = $toConnection
$proxy.SetItemDataSources($ssrsItem.Path, $newDataSource);
Write-Host " DOES NOT WORK !" -ForegroundColor RED
Write-Host ""
}
}
}
}
}
}
}
SetConnection "msbireports" "/2134_DELIVRI/Test/PS1" "EDUCFI_SSAS" "/1446_EDUCFI/Datasources/EDUCFI_SSAS" "replace"
This is driving me nuts. I also tried to do this differently, that is building an array of DataSources and at the end of the report's handling pushing it to with SetItemDataSources but everytime, those two cases failed, with a weird error, telling me it can't convert a DataSource to.. a DataSource. (?!)
My sanity begs for your help !
