search

mmajcica / DeploySsrs

Build-Release task for VSTS/TFS that manages Microsoft's SQL Server Reporting Services
MIT License
21 stars 21 forks source link

Question: Reference Datasource in Reports without overwriting the Datasource #68

Open AlexAlex1337 opened 3 years ago

AlexAlex1337 commented 3 years ago

Dear author mmajcica,

I have several reports that use the same Shared Datasource and two environments (TEST/PROD).

The Shared Datasource is called ODS1 and uses Windows Credentials with Username/PWD saved in the Datasource. TEST and PROD environment have the ODS1 in the same location, but use different Connection Strings and Username/PWD.

The idea is that these ODS1 are manually created in TEST/PROD environment and should not be changed or overwritten by the DevOps Pipelines. Reports should be deployed/overwritten by DevOps pipelines to both environments.

At the moment it seems that this is not possible. When I just have the reports in the configuration json, they get deployed but the Datasource is not referenced and that needs to be manually done after deployment. When I have the datasource and the reports in the configuration json, then the datasource gets properly referenced in the reports which is fine if you have just one environment, but at the moment it creates the Datasource on PROD with the Connectionstring/User/PWD of the TEST environment.

Is there an option to NOT overwrite existing Datasources, but overwrite existing reports and still reference the Datasources in the reports after deploying? The Datasources are named identically in both environments and are at the same path, so that should not be an issue.

best regards Alex

AlexAlex1337 commented 3 years ago

OK, I solved it and just deploy the reports and then use some functions I found via Google in combination with Pipeline Variables to update the datasource reference. Here is the code in case you want to implement something into your project.

function Global:Get-SSRS { [CmdletBinding()] param( 

    [Parameter(Mandatory=$true)]
    [string] $ReportServerUri

)

[string] $fn = $MyInvocation.MyCommand
[string] $stepName = "Begin [$fn]"   

[string] $reportServerURIOverride = $ReportServerUri
[object] $returnObj = $null

try
{        

    $stepName = "[$fn]: Validate parameters"
    #--------------------------------------------
    Write-Verbose $stepName 

    #Append the "?wsdl" at the end if it does not already have it
    # e.g., 'http://MYSERVER/MYINSTReportServer/ReportService2010.asmx?wsdl'

    if (-not $reportServerURIOverride.EndsWith('?wsdl'))
    {
        $reportServerURIOverride = ("$reportServerURIOverride"+'?wsdl')
    }

    $stepName = "[$fn]: Open SSRS url"
    #--------------------------------------------
    Write-Verbose $stepName 

    $URI = New-Object System.Uri($ReportServerUri)
    $SSRS = New-WebServiceProxy -Uri $URI -UseDefaultCredential

    $returnObj = $SSRS

    #Return value
    ,$returnObj       

}
catch
{
    [Exception]$ex = $_.Exception
    Throw "Unable to get SSRS reference. Error in step: `"{0}]`" `n{1}" -f `
                    $stepName, $ex.Message
}
finally
{
    #Return value if any
}

}

function Global:Get-SSRSReport { [CmdletBinding()] param( 

    [Parameter(Mandatory=$true)]
    [string] $ReportServerUri,

    [Parameter(Mandatory=$false)]
    [string] $ReportFolderPath = '/',

    [Parameter(Mandatory=$false)]
    [string] $ReportName = ''

)

[string] $fn = $MyInvocation.MyCommand
[string] $stepName = "Begin [$fn]"   

#-----------------------------------
#We need to collect all these inputs!
#-----------------------------------
[object[]] $returnObj = @()

try
{        

    $stepName = "[$fn]: Validate parameters"
    #--------------------------------------------
    Write-Verbose $stepName 

    $stepName = "[$fn]: Open SSRS url"
    #--------------------------------------------
    Write-Verbose $stepName 

    $SSRS = Get-SSRS -ReportServerUri $ReportServerUri

    $stepName = "[$fn]: Get all the reports"
    #--------------------------------------------
    Write-Verbose $stepName 

    $reports = $SSRS.ListChildren($ReportFolderPath, $true) |
                            Where-Object {$_.TypeName -eq 'Report'}

    #Result will be of the form (first element):
    <#         $reports[0]             ID                    : 0b767817-1c39-4a86-98e5-ab372f80dc64             Name                  : MyReport             Path                  : /MyApp/MyReport             VirtualPath           :              TypeName              : Folder             Size                  : 0             SizeSpecified         : False             Description           :              Hidden                : False             HiddenSpecified       : False             CreationDate          : 1/14/2015 11:11:43 AM             CreationDateSpecified : True             ModifiedDate          : 1/14/2015 11:11:59 AM             ModifiedDateSpecified : True             CreatedBy             : MYUSERID             ModifiedBy            : MYUSERID             ItemMetadata          : {}         #>

    $stepName = "[$fn]: Filter to specific report among the reports"
    #--------------------------------------------
    Write-Verbose $stepName 

    if ($ReportName.Trim().Length -gt 0)
    {
        <#             #Combine the folder and the report name to form full path (without the file extension if any)             $reportFullPath =  Join-Path `                                 -Path $ReportFolderPath `                                 -ChildPath ([system.io.fileinfo]$ReportName).BaseName             #>

        #Not doing it with the full path would allow us to search all reports in all paths by name starting from the root!
        $report = ($reports |
                        Where-Object {$_.Name -eq ([system.io.fileinfo]$ReportName).BaseName}) #Eliminate file extension

        $returnObj = @($report)
    }
    else
    {
        $returnObj = $reports
    }

    #Return value
    ,$returnObj       

}
catch
{
    [Exception]$ex = $_.Exception
    Throw "Unable to get SSRS reports. Error in step: `"{0}]`" `n{1}" -f `
                    $stepName, $ex.Message
}
finally
{
    #Return value if any
}

}

function Global:Get-SSRSSharedDataSource { [CmdletBinding()] param( 

    [Parameter(Mandatory=$true)]
    [string] $ReportServerUri,

    [Parameter(Mandatory=$false)]
    [string] $DataSourcePath = '/',

    [Parameter(Mandatory=$false)]
    [string] $DataSourceName = ''

)

[string] $fn = $MyInvocation.MyCommand
[string] $stepName = "Begin [$fn]"   

#-----------------------------------
#We need to collect all these inputs!
#-----------------------------------
[string] $dataSourcePathOverride = $DataSourcePath
[object[]] $returnObj = @()

try
{        

    $stepName = "[$fn]: Validate parameters"
    #--------------------------------------------
    Write-Verbose $stepName 

    $stepName = "[$fn]: Open SSRS url"
    #--------------------------------------------
    Write-Verbose $stepName 

    $SSRS = Get-SSRS -ReportServerUri $ReportServerUri

    $stepName = "[$fn]: Get all the datasources"
    #--------------------------------------------
    Write-Verbose $stepName 

    if ($dataSourcePathOverride.Trim().Length -eq 0)
    {
        $dataSourcePathOverride = '/'  #Could use "/Data Sources" but there could be data sources in other locations!
    }

    #Get the datasources
    $dataSources = $SSRS.ListChildren($dataSourcePathOverride, $true) |
                            Where-Object {$_.TypeName -eq 'DataSource'}

    #$dataSources = $SSRS.GetItemDataSources($dataSourcePathOverride)

    #Result will be of the form (first element):
    <#         $DataSources[0]             ID                    : b58a4feb-c3ad-49c8-a699-a4f52885d09d             Name                  : My DataSourceName             Path                  : /Data Sources/MyDataSourceName             VirtualPath           :              TypeName              : DataSource             Size                  : 467             SizeSpecified         : True             Description           :              Hidden                : False             HiddenSpecified       : False             CreationDate          : 1/14/2015 11:11:43 AM             CreationDateSpecified : True             ModifiedDate          : 2/25/2015 12:14:16 PM             ModifiedDateSpecified : True             CreatedBy             : domain\SOMEUSER             ModifiedBy            : domain\SOMEUSER             ItemMetadata          : {}         #>

    $stepName = "[$fn]: Filter to specific datasource if one was asked for"
    #--------------------------------------------
    Write-Verbose $stepName 

    if ($DataSourceName.Trim().Length -gt 0)
    {
        $dataSource = ($dataSources |
                        Where-Object {$_.Name -eq $DataSourceName}) 

        $returnObj = @($dataSource)
    }
    else
    {
        $returnObj = $dataSources
    }

    #Return value
    ,$returnObj

}
catch
{
    [Exception]$ex = $_.Exception
    Throw "Unable to get SSRS data sources. Error in step: `"{0}]`" `n{1}" -f `
                    $stepName, $ex.Message
}
finally
{
    #Return value if any
}

}

function Global:Set-SSRSReportDataSource { [CmdletBinding()] param( 

    [Parameter(Mandatory=$true)]
    [string] $ReportServerUri,

    [Parameter(Mandatory=$false)]
    [string] $ReportFolderPath = '/',

    [Parameter(Mandatory=$true)]
    [string] $ReportName,

    [Parameter(Mandatory=$false)]
    [string] $DataSourcePath = '/',

    [Parameter(Mandatory=$true)]
    [string] $DataSourceName

)

[string] $fn = $MyInvocation.MyCommand
[string] $stepName = "Begin [$fn]"   

[HashTable]$params = @{
        'ReportServerUri' = $ReportServerUri;
        'ReportFolderPath' = $ReportFolderPath;
        'ReportName' = $ReportName;
        'DataSourcePath' = $DataSourcePath;
        'DataSourceName' = $DataSourceName}

#-----------------------------------
#We need to collect all these inputs!
#-----------------------------------
[string] $reportServerURIOverride = $ReportServerUri   

try
{        

    $stepName = "[$fn]: Validate parameters"
    #--------------------------------------------
    Write-Verbose $stepName 

    $stepName = "[$fn]: Get data source: [{0}]" -f $DataSourceName
    #--------------------------------------------
    Write-Verbose $stepName 

    $dataSource = Get-SSRSSharedDataSource `
                    -ReportServerUri $ReportServerUri `
                    -DataSourcePath $DataSourcePath `
                    -DataSourceName $DataSourceName

    if (($dataSource.Count -eq 0) `
            -or ($dataSource.Count -gt 1))
    {
        Throw "[$fn]: Found no matches or multiple data source matches for parameters: [{0}]" -f ($params | Out-String)
    }

    $stepName = "[$fn]: Get report"
    #--------------------------------------------
    Write-Verbose $stepName 

    $report = Get-SSRSReport `
                    -ReportServerUri $ReportServerUri `
                    -ReportFolderPath $ReportFolderPath `
                    -ReportName $ReportName

    if (($report.Count -eq 0) `
            -or ($report.Count -gt 1))
    {
        Throw "[$fn]: Found no matches or multiple report matches for parameters: [{0}]" -f ($params | Out-String)
    }

    $stepName = "[$fn]: Open SSRS url"
    #--------------------------------------------
    Write-Verbose $stepName 

    $SSRS = Get-SSRS -ReportServerUri $ReportServerUri

    $stepName = "[$fn]: Update the data source"
    #--------------------------------------------
    Write-Verbose $stepName 

    #https://blogs.infosupport.com/managing-ssrs-reports-with-powershell/
    #http://powershell-with-dave.blogspot.com/2014/08/editing-ssrs-shared-data-sources-with.html

    #The most useful link that finally helped me make this work!
    #http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell

    $dataSources = $SSRS.GetItemDataSources($report[0].Path)

    $dataSources | ForEach-Object {
        $proxyNamespace = $SSRS.GetType().Namespace

        $dataSourceNew =  New-Object ("$proxyNamespace.DataSource")                   #New-Object SSRS.DataSource
        $dataSourceNew.Name = $dataSource[0].Name
        $dataSourceNew.Item = New-Object ("$proxyNamespace.DataSourceReference")      #New-Object SSRS.DataSourceReference
        $dataSourceNew.Item.Reference = $dataSource[0].Path

        $_.Item = $dataSourceNew.Item
        $SSRS.SetItemDataSources($report[0].Path, $_)

        #$SSRS.SetItemDataSources($report[0].Path, @($dataSourceNew))
    }

    <#         #The above method did not work initially so tried this but this did not work either! Left here for reference in case we need to more things!         #http://stackoverflow.com/questions/36334676/deploying-ssrs-rdl-files-from-vb-net-issue-with-shared-datasources?rq=1         $itemRef = New-Object ("$proxyNamespace.ItemReference")         $itemRef.Name = $dataSource[0].Name         $itemRef.Reference = $dataSource[0].Path         $SSRS.SetItemReferences($report[0].Path, @($itemRef))         #>

    #Return value        

}
catch
{
    [Exception]$ex = $_.Exception
    Throw "Unable to set SSRS report data source. Error in step: `"{0}]`" `n{1}" -f `
                    $stepName, $ex.Message
}
finally
{
    #Return value if any
}

}

Set-SSRSReportDataSource `
        -ReportServerUri 'https://$(PBIRS)/ReportServer/ReportService2010.asmx' `
        -ReportFolderPath '$(ReportFolderPath)' `
        -ReportName '$(ReportName)' `
        -DataSourcePath '$(DataSourcePath)' `
        -DataSourceName '$(DataSourceName)'
steveking123 commented 2 years ago

Hi Alex

What I have found is that deploying an update to an existing report is fine anyway - it retains whatever datasource linking it already has. So this is only an issue for new reports.

That script does the job well for new reports so thanks for that. I think I will look to build it into my pipeline release by putting it into a standard-named file in the project with the relevant values for the relevant commit - we already have to do that for the extension's deployment configuration json file.

It would be really great if the extension itself could link up datasources like this though. I have used SSRS at two sites, and both have their datasources defined independently on the server and not in the project.

Thanks again for the script.

Steve