SSRS RDL Files to Find Data source reference?

Question

using SQL scripts what would we write to find the shared data source reference (highlighted in green) as well as the name in the blue box in SSRS rdl files?

I am looking for a list to pull all the report names, the data source names (in blue box) and the actual data source reference (in the green box) on the server that is used. There is a lot of overlap of names used and we need to clean up reports. (there are too many to do this manually).

Using sql scripts? Do you mean you want to inspect the reports on the SQL Server? They are stored as a varbinary (see SELECT Content FROM ReportServer.dbo.[Catalog];), and Microsoft don't supply decryption key. You'll need to check the files you have stored locally. Easiest way would be via PowerShell. — Larnu
Yes, i want to login to the server via sql management studio and use scripts to pull this data instead of manually reviewing hundreds of reports that exist. what code do i use? i can't find the appropriate tables to reference. — Elizabeth
You can't do this on the deploy reports. You'll need to inspect the local files (for example using a PoSh script). — Larnu
SELECT Content FROM ReportServer.dbo.[Catalog] gives a lot of memory type data, mostly hex values. no concrete strings. a very long series of hex memory addresses. — Elizabeth
yes, and when you convert content using CONVERT(xml, CONVERT(varbinary(max), content)) you get a massive string of semi-sensical data. i am still not sure how to pull out that field specifically — Elizabeth

YelizavetaYR YelizavetaYR · Accepted Answer · 2018-03-28T19:31:40

Answer can be found here: Listing all Data Sources and their Dependencies (reports, items, etc) in SQL Server 2008 R2

SELECT
    C2.Name AS Data_Source_Name,
    C.Name AS Dependent_Item_Name,
    C.Path AS Dependent_Item_Path
FROM
    ReportServer.dbo.DataSource AS DS
        INNER JOIN
    ReportServer.dbo.Catalog AS C
        ON
            DS.ItemID = C.ItemID
                AND
            DS.Link IN (SELECT ItemID FROM ReportServer.dbo.Catalog
                        WHERE Type = 5) --Type 5 identifies data sources
        FULL OUTER JOIN
    ReportServer.dbo.Catalog C2
        ON
            DS.Link = C2.ItemID
WHERE
    C2.Type = 5
ORDER BY
    C2.Name ASC,
    C.Name ASC;

SSRS RDL Files to Find Data source reference?

1 Answers