Programmatically set data source link for SSRS Report files

Question

I have a scenario where I export/import a site collection with the Content Deployment Wizard. The problem is that all Reporting Services (SQL Server 2008 R2) reports (all located in the same document library) drops their binding to the shared datasource file (located in another document library).

My question is:

Is there a way to programmatically (C#) iterate through each report in a document library and set the data source link property?

Answer 1

Ended up solving this myself using Powershell instead of C# (using mock-variables in this snippet). The Powershell script must be executed from the SharePoint server and under administrator privileges (both windows & SharePoint I believe).

Edit: Also note that my SiteCollection is the root web for the web application

#Powershell
Clear-Host

# Add SharePoint snapin if needed
if ((Get-PSSnapin -Name Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue)     -eq $null)
{
    Add-PSSnapin Microsoft.SharePoint.Powershell
}

# Variables
$webApplicationUrl = "http://my.hostheader.com/"
$hostHeader = $webApplicationUrl -replace "http://", ""
$connectionString = "Data Source=MyDbServer;Initial Catalog=MyDataBase;Integrated    Security=SSPI;" 
$webApplicationUNCPath = "\\$hostHeader\DavWWWRoot\"
$dataSourcesLibraryName = "Data Sources"
$dataSourceDefinitionName = "MySharedDataSource.rsds"
$dataSourceLink =    "$webApplicationUrl/$dataSourcesLibraryName/$dataSourceDefinitionName"
$dataSourceUNCFilePath = $webApplicationUNCPath + $dataSourcesLibraryName + "\" +     $dataSourceDefinitionName
$reportsLibraryName = "MyReports"
$reportServerUri = "$webApplicationUrl/_vti_bin/ReportServer/ReportService2010.asmx?WSDL"

# Script
$site = Get-SPSite($webApplicationUrl)
$web = $site.OpenWeb()

$dataSources = $web.Lists | Where-Object { $_.Title -eq $dataSourcesLibraryName }
$dataSource = $dataSources.Items | Where-Object { $_.Name -eq   $dataSourceDefinitionName }
$reports = $web.Lists | Where-Object { $_.Title -eq $reportsLibraryName }

## Change properties on the shared data source object (through xml manipulation)
$xml = [xml](Get-Content $dataSourceUNCFilePath)
$xml.DataSourceDefinition.ConnectString = $connectionString
$xml.DataSourceDefinition.CredentialRetrieval = "Integrated"
$xml.DataSourceDefinition.Enabled = "True"
$windowsCredentialsNode = $xml.DataSourceDefinition.ChildNodes | Where-Object { $_.Name -eq "WindowsCredentials" }
if ($windowsCredentialsNode -ne $NULL)
{
    $xml.DataSourceDefinition.RemoveChild($windowsCredentialsNode)
}
$ImpersonateUserNode = $xml.DataSourceDefinition.ChildNodes | Where-Object { $_.Name -eq "ImpersonateUser" }
if ($ImpersonateUserNode -ne $NULL)
{
    $xml.DataSourceDefinition.RemoveChild($ImpersonateUserNode)
}
$xml.Save($dataSourceUNCFilePath)

# Iterate through reports and set correct shared datasource
$Proxy = New-WebServiceProxy -Uri $reportServerUri -UseDefaultCredential ;

# Gather the webservice types for later use 
$WebServiceTypes = @{}
foreach ($Type in $Proxy.GetType().Assembly.GetExportedTypes())
{
    $WebServiceTypes.Add($Type.Name, $Type.FullName);
}

# Get list of all reports
$ReportItems = $Proxy.ListChildren("/", $true) | Where-Object { $_.Name -Like "*.rdl" } | Where-Object { $_.Path -Like "$webApplicationUrl/$reportsLibraryName/*" }
$dataSources = $web.Lists | Where-Object { $_.Title -eq $dataSourcesLibraryName }
$dataSource = $dataSources.Items | Where-Object { $_.Name -eq     $dataSourceDefinitionName }

# Declare DataSourceReference object
$ref = New-Object $WebServiceTypes.DataSourceReference
$ref.Reference = $dataSourceLink;

# Declare DataSourceReference object
$ds = New-Object $WebServiceTypes.DataSource
$ds.Name = $dataSource.DisplayName
$ds.Item = $ref

# Apply DataSource to all Reports
foreach ($ReportItem in $ReportItems)
{
    $dss = $Proxy.GetItemDataSources($ReportItem.Path)      
    $dss[0] = $ds
    Try 
    {
        $Proxy.SetItemDataSources($ReportItem.Path, $dss)
    }
    Catch [System.Exception]
    {
        "Error: Could not fix datasource for report: {0}" -f $ReportItem.Name
    }
}
$Proxy.Dispose()
$site.Dispose()