locked
Data Source reference in rdl file RRS feed

  • Question

  • Guys,

    I am working in a project to get SSRS metadata dependencies and I was recommended to use the article Extracting SSRS Report RDL (XML) from the ReportServer database. This article is a really good material.

    However, I still have some issues related to Data Sources. When I try to get the information from rdl file is the only valid Data Source Reference I was able to find is the Data Source name, but the Data Source is not in the same library of the report, so it should have a path and it does not have it.

      <DataSources>
        <DataSource Name="Products">
          <DataSourceReference>Products</DataSourceReference>
          <rd:SecurityType>Integrated</rd:SecurityType>
          <rd:DataSourceID>d6de0a33-9824-4f73-ad87-729c7ce2f4ce</rd:DataSourceID>
        </DataSource>
      </DataSources>
    

    If I use just the name, I won't be able to differentiate two different data sources having the same name in different libraries, for example. So, how can I know wich is the right data source file using the rdl file?

    Regards.

    Lawrence


    Lawrence Carvalho

    Wednesday, June 29, 2016 1:57 PM

Answers

  • Sorry about that, I was thinking of shared data sets. The data source information in the RDL is a little vague at times. You can use the table entries in the DataSource table to find the shared data source details.

    SELECT RPT.Path AS ReportPath
      , RPT.name AS ReportName
      , CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS ReportXML
      , SDS.name AS SharedDataSourceName
      , SDS.Path AS SharedDataSourcePath
      , CONVERT(xml, CONVERT(varbinary(max), SDS.content)) AS SharedDataSourcetXML
    FROM [Catalog] AS RPT
    LEFT JOIN [dbo].[DataSource] AS SDSLink ON RPT.ItemID = SDSLink.ItemID
    LEFT JOIN [dbo].[Catalog] AS SDS ON SDSLink.Link = SDS.ItemID
    WHERE RPT.Type = 2
     AND  SDS.name IS NOT NULL

    Wednesday, June 29, 2016 8:11 PM

All replies

  • The XML value for the element rd:DataSourceID is the unique identifier of the datasource in the SSRS Catalog table. The path column of that record will tell you where it is located.

    cheers!

    Wednesday, June 29, 2016 3:16 PM
  • Hi James,

    Thanks for your answer. I have tried that before, but it didn't work. I was not able to find that DataSourceID there. I should had put that in the first post.

    Any idea?


    Lawrence Carvalho

    Wednesday, June 29, 2016 3:36 PM
  • Sorry about that, I was thinking of shared data sets. The data source information in the RDL is a little vague at times. You can use the table entries in the DataSource table to find the shared data source details.

    SELECT RPT.Path AS ReportPath
      , RPT.name AS ReportName
      , CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS ReportXML
      , SDS.name AS SharedDataSourceName
      , SDS.Path AS SharedDataSourcePath
      , CONVERT(xml, CONVERT(varbinary(max), SDS.content)) AS SharedDataSourcetXML
    FROM [Catalog] AS RPT
    LEFT JOIN [dbo].[DataSource] AS SDSLink ON RPT.ItemID = SDSLink.ItemID
    LEFT JOIN [dbo].[Catalog] AS SDS ON SDSLink.Link = SDS.ItemID
    WHERE RPT.Type = 2
     AND  SDS.name IS NOT NULL

    Wednesday, June 29, 2016 8:11 PM
  • Perfect, James!!!! :-)

    Cheers

    Lawrence


    Lawrence Carvalho

    Thursday, June 30, 2016 12:10 PM