SSRS SharePoint Foundation 2010 Integration - Deployment Query


  • Hello!

    I have a query regarding the deployment of a SQL 2008 R2 reporting solution with Shared Data Sources and Shared Datasets to a SharePoint Foundation 2010 solution.

    We have an isolated test environment that I can't access from my BIDS solution, so deployment through BIDS is not possible (unless I install BIDS in the test environment - but I would like to avoid this if possible).  Ideally I'm looking for a scripted solution.

    I've taken advantage of some scripts I've found (here's an example: to publish the Shared Data Sources and Shared Datasets to the libraries in SharePoint:

    # Define variables $siteCollectionURL = "http://MyServer" $sourceFolder = "C:\SSRSDeploy" $targetRptLib = $siteCollectionURL+"/Reports" $targetDCL = $targetRptLib+"/Data Sources" $targetDSL = $targetRptLib+"/Datasets" $rsProxyEndpt = $siteCollectionURL+"/_vti_bin/ReportServer/ReportService2006.asmx" # Define Data Source variables $newAdminDataSourceName = "$targetDCL/DS_AdminDB.rsds"; $newDWDataSourceName = "$targetDCL/DS_DataWarehouseDB.rsds"; $newTXDataSourceName = "$targetDCL/DS_TransactionDB.rsds"; function publish-DataSource ([string] $dsReference) { #Load the data source Xml [xml] $DSXml = Get-Content ($dsReference); #Initialize a DataSourceDefinition object [DataSourceDefinition] $dsDefinition = New-Object DataSourceDefinition #Initialize a DataSource object [DataSource] $dSource = New-Object DataSource $dSource.Item = $dsDefinition #Read the settings from XML and populate related props $dsDefinition.Extension = $DSXml.RptDataSource.ConnectionProperties.Extension $dsDefinition.ConnectString = $DSXml.RptDataSource.ConnectionProperties.ConnectString $dsDefinition.ImpersonateUserSpecified = $true $dsDefinition.Prompt = $null $dsDefinition.WindowsCredentials = $true $dsDefinition.CredentialRetrieval = [CredentialRetrievalEnum]::Integrated $dSource.Name = $DSXml.RptDataSource.Name $dsFileName = [String]::Concat($DSXml.RptDataSource.Name.Trim(),".rsds") $rsdsAbsoluteUrl = [string]::Concat($targetDCL.TrimEnd("/"),"/",$dsFileName) #Publish the data source to the data connection library $void = $rs06.CreateDataSource($dsFileName, $targetDCL, $true, $dsDefinition, $null) WRITE-HOST -FOREGROUND Yellow 'Successfully converted data source:' $dsFileName }

    function publish-DataSet ([string] $dsReference){
    #Load the data source Xml 
    [xml] $DSXml = Get-Content ($dsReference); 
    $file = Get-Childitem ($dsReference);

    $site=Get-SpSite $siteCollectionURL
    $rsdFolder = $web.GetFolder($targetDSL)

    $desturl = $targetDSL+"/"+$file.Name
    #Write-Host "Name is: " $file.Name

    (Get-Content $dsReference) | % {$_ -replace "</DataSourceReference>",".rsds</DataSourceReference>"} | Set-Content -path $dsReference

    #Write-Host "Processing Completed"
    $fileCheckedOut = "N"
    $fileCheckedOut = "Y"
    $stream = [IO.File]::OpenRead($dsReference)
    $resultingfile =$rsdFolder.files.Add($desturl,$stream,$true)

    if($fileCheckedOut -eq "Y")
    $rsdFile.CheckIn("Deployment Script")

    WRITE-HOST 'Successfully Deployed Data Set:' $file.Name

    $void=[Reflection.Assembly]::LoadFrom("$pwd\ReportingService2006.dll") $rs06 = New-Object ReportingService2006 $rs06.Url = $rsProxyEndpt; $rs06.Credentials=[System.Net.CredentialCache]::DefaultCredentials $sourceFolder = $sourceFolder #Publish Data sources [Object[]] $dataSourcesToPublish = [System.IO.Directory]::GetFiles($sourceFolder, "*.rds"); $dataSourcesToPublish | % { publish-DataSource $_ };

    #Publish Data Sets 
    [Object[]] $dataSetsToPublish = [System.IO.Directory]::GetFiles($sourceFolder, "*.rsd");
    $dataSetsToPublish | % { publish-DataSet $_ };

    I then publish the reports as follows ...

    function publish-Report ([string] $rptReference)
    	[Warning[]]$warnings = $null
    	#Add datasource and dataset extensions
        (Get-Content $rptReference) | % {$_ -replace "</DataSourceReference>",".rsds</DataSourceReference>"} | Set-Content -path $rptReference
        (Get-Content $rptReference) | % {$_ -replace "</SharedDataSetReference>",".rsd</SharedDataSetReference>"} | Set-Content -path $rptReference
    	#Get the RDL Item
    	$rptFileInfo = Get-Item ($rptReference);
    	$rptName = $rptFileInfo.Name;	
    	#Load the RDL Xml 
    	[xml] $rptXml = Get-Content ($rptReference);
    	#Extract the data source used by report
    	$localDSName = $rptXml.Report.DataSources.DataSource.Name	
    	#Extract the shared data sets used by report
    	$localSDSName = $rptXml.Report.DataSets.DataSet.Name	
    	#Load the RDL Binary 
    	[Byte[]]$rptContent = Get-Content $rptReference -Encoding byte 
    	#Publish the report to the Report library
        [CatalogItem] $item = $rs06.CreateReport($rptName,$targetRptLib,$true,$rptContent,$null,[ref] $warnings )
    	WRITE-HOST -FOREGROUND GREEN 'Successfully published report:' $rptName
    	#Fix up the data source
    	$rptAbsoluteUrl = [string]::Concat($targetRptLib.TrimEnd("/"),"/",$rptName)
    	$dataSources = $rs06.GetItemDataSources($rptAbsoluteUrl)
    	$dataSources | ForEach-Object {
    		# check the name of the Data Source and apply the correct reference to the variable
    		switch($_.Name) {
    			"DS_DataWarehouseDB" {$newDataSourceName = $newDWDataSourceName}
    			"DS_MoJPPIAdminDB" {$newDataSourceName = $newAdminDataSourceName}
    			"DS_TransactionDB" {$newDataSourceName = $newTXDataSourceName}
    		$proxyNamespace = $_.GetType().Namespace
    		$myDataSource = New-Object ("$proxyNamespace.DataSource")
    		$myDataSource.Name = $newDataSourceName
    		$myDataSource.Item = New-Object ("$proxyNamespace.DataSourceReference")
    		$myDataSource.Item.Reference = $newDataSourceName
    		$_.item = $myDataSource.Item
    		$rs06.SetItemDataSources($rptAbsoluteUrl, $_)
    #Publish reports
    [Object[]] $reportsToPublish = [System.IO.Directory]::GetFiles($sourceFolder, "*.rdl");
    $reportsToPublish | % { publish-Report $_ };

    ... and this works great at resolving the links between the reports and the Shared Data Sources (which you would do manually through the "Manage Shared Data Sources" option on the report context menu).

    However, I cannot find anything that will allow me to do the same with the Shared Datasets, and I have to go through each report and manually reconfigure the links to the appropriate Shared Dataset.

    Is anyone aware of a way of scripting out this process as this is a very time consuming process, and one that I'm going to have to repeat when we do a deployment to production?

    Many thanks,


    Friday, August 31, 2012 8:44 AM


All replies