Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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()
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.