Blogs

SSRS – Delete specific RDL files from Report Server in one go

Inkey, January 11, 2018 8214 Views

Introduction
Earlier, we wrote a blog to download the multiple report files (.rdl files) at one go. You can find it here. Today, following readers’ requests, I will share a simple handy script which will help you to delete specific report files at once.

Implementation
SSRS uses SQL Server to store it’s details and the Catalog and DataSource tables are used to store the report file details. The below script simply deletes the report from the Catalog and DataSource tables.

--Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer]
USE [ReportServer]

--Replace NULL with keywords of the ReportManager's Report Path, 
--if reports from any specific path are to be deleted
DECLARE @FilterReportPath AS VARCHAR(500) = NULL
 
--Replace NULL with the keyword matching the Report File Name,
--if any specific reports are to be deleted
DECLARE @FilterReportName AS VARCHAR(500) = NULL
 
--Used to prepare the dynamic query
DECLARE @TSQL AS NVARCHAR(MAX)
 
   --Prepare the query to delete the report files.
  
   SET @TSQL = STUFF((SELECT
                      '; ' +
                      ' ;DELETE ' +
                      ' FROM ' +
                      ' [dbo].[DataSource] ' +
                      ' WHERE ' +
                      ' ItemID = ''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''+
                      ' ;DELETE ' +
                      ' FROM ' +
                      ' [dbo].[Catalog] ' +
                      ' WHERE ' +
                      ' ItemID = ''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''
                    FROM
                      [dbo].[Catalog] CL
                    WHERE
                      CL.[Type] = 2 --Report
                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')
                      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name)
                    FOR XML PATH('')), 1,1,'')
  
  --SELECT @TSQL
  
  --Execute the Dynamic Query
  EXEC SP_EXECUTESQL @TSQL

Happy Reporting!