Shared DataSets are not referenced correctly on deployment

[phkrug]

With the recent updates of Deploy SSRS my reports are not linked correctly against the deployed Data Sources. My deployment config looks like the following. The Data Sources are changed depending on the environment. The replacements work just fine. Until the updates the Data Sources were linked via the name. The name of the shared data source in the report had to be the same like the deployed Data Source from the XML. Everything just worked fine.

<?xml version="1.0" encoding="utf-8"?>
<Folder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="Root">
  <Folders>
    <Folder Name="DWH" InheritParentSecurity="true" CleanExistingItems="false" Overwrite="false">
      <Folders>
        <Folder Name="Datasources" Hidden="true" CleanExistingItems="false" Overwrite="true">
          <DataSources>
            <DataSource ConnectionString="Data Source=#{SqlServerName}#;Initial Catalog=#{SqlDatabaseName}#" Name="DWH" Extension="SQL" CredentialRetrieval="Store" UserName="#{DS_UserName}#" Password="#{DS_Password}#" WindowsCredentials="True" />
          </DataSources>
        </Folder>

         <Folder Name="Folder 1" inheritParentSecurity="false" overwrite="false" CleanExistingItems="true">
          <Reports>
            <Report Name="Report 1" FileName="Report1.rdl" /> <!-- Contains a reference to a shared datasource named 'DWH' -->
          </Reports>
        </Folder>

      </Folders>
    </Folder>
  </Folders>
</Folder>

Now the reports say that the Data Source could not be found. I don't know if the previous deployment behavior was intentional or not. But it worked for my config. Now all of my reports lack the link to the Data Source.

This warning is still present. But this was mentioned in issue #14 quite a while ago. As far as I understood this is a response from the SSRS API. This warning was already present while it worked fine.

[warning]The dataset 'DWH' refers to the shared data source 'DWH', which is not published on the report server. The shared data source 'DWH' must be published before this report can run.

My current workaround would be to switch back to the old version of SSRS Deploy by manipulating the agents work directory. Version 3.1.10 seems to work fine. This would probably be overridden when the Azure DevOps Server updates the extension again.

[GregoryOtt]

• Has the DataSource "DWH" been created by the extension?
• Do you have a warning like "The reference for datasource DWH can not be found."?

[phkrug]

Yes and yes.

The DataSources are created on every deployment. I also manually deleted them before a deployment and they were recreated. The warning is also present in the logs. But this warning also appears if I use version 3.1.10 where the deployment results in working reports with the correct link to the DS.

[GregoryOtt]

@phkrug can you share logs?

[phkrug]

Which logs would you like to see? The debug info from the Azure DevOps pipeline?

[GregoryOtt]

Yep

[phkrug]

SSRS_Deploy_Log_phkrug.txt I removed the clutter from all the other reports. It's just one folder, one report and two DataSources. I hope I didn't cut out or rename too much.

[GregoryOtt]

Can you send the beginning of the report "Report1" with the DataSource section and at least one DataSet?

[phkrug]

I have no shared DataSets to deploy. I don't know why it iterates that many times through "Publish-DataSet". And there is no beginning for Report1 before this line. ##[debug]Entering script New-SsrsReport

[GregoryOtt]

I mean the xml of the "Report1" :)

[phkrug]

Oh, of course.

<?xml version="1.0" encoding="utf-8"?>
<Report MustUnderstand="df" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily">
  <Description></Description>
  <df:DefaultFontFamily>Calibri</df:DefaultFontFamily>
  <AutoRefresh>0</AutoRefresh>
  <InitialPageName>Info</InitialPageName>
  <DataSources>
    <DataSource Name="DWH">
      <DataSourceReference>DATABASE</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>cd6c7fc1-1a8a-4365-b38a-d6f71f5cbd27</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="Report1">
      <Query>
        <DataSourceName>DWH</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@Param1">
            <Value>=Parameters!Param1.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>SELECT * FROM mart.func_TVF1(@Param1, default)</CommandText>
      </Query>

I see where this is going. Could it be that the link between the DataSource and the deployed report is now established over the DataSourceID? Because the ID in that snippet is from the DS in the VS Solution and not the deployed one.

[GregoryOtt]

Your deployed Datasource is named : "DWH" according to the XML that deploy the artifacts. In your report the DataSourceReference points to "DATABASE". The two names must be aligned as now we are looking for the DataSourceReference to update the report's DataSource

[phkrug]

Which makes sense to me. It was kind of odd that the internal name of the shared DataSource in the report was magically matched against the DataSource name which was deployed.

Although this is quite a breaking change. I'm not happy how the Azure DevOps Server handles updates of extensions. But thank you for this analysis. I will look into changing that in all our reports after testing.

[phkrug]

Just for documentation and completeness: I just refactored all our reports to use the correct DataSourceReference. Now the deployment with version 3.1.12 works as intended and all reports work again.

This issue is solved and the extension works as designed.

[mmajcica]

Does this require any documentation update?

On Wed, 14 Apr 2021, 10:46 phkrug, @.***> wrote:

Closed #73 https://github.com/mmajcica/DeploySsrs/issues/73.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/mmajcica/DeploySsrs/issues/73#event-4592286498, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAXC45ZK3RU2ERTVFK4YNNTTIVJAHANCNFSM423DJ7UQ .

[phkrug]

I think it's already documented. If you know about those relations from reports and shared DataSources, it should be clear how this works for the deployment.

Reference DataSources: If selected the DataSources in the configuration file will be referenced in the Reports, by matching the DataSource DataSourceReference value.