8/07/2013

SSRS – Download all RDL files from Report Server in one go.

Introduction

People working on SSRS are well aware that “Report Manager” does not support downloading all the report files (.rdl files) at one go out-of-box. And I am sure that many of you might have come across this requirement some day or other. Hence, today I will share a simple handy script which would help you to download all the required report files at once.

Implementation

SSRS uses SQL Server to store it’s details as a backend and the Catalog table is used to store the report file in binary form. The below script simply pulls the report definition from the Catalog table & uses BCP utility to export the same at a pre-defined path as a .rdl file.

To use the BCP utility from TSQL, we need to execute  “xp_cmdshell” command; it is disabled by default. So, first you need to execute the below script to enable it -

-- Allow advanced options to be changed.



EXEC sp_configure 'show advanced options', 1



GO



 



-- Update the currently configured value for advanced options.



RECONFIGURE



GO



 



-- Enable xp_cmdshell



EXEC sp_configure 'xp_cmdshell', 1



GO



 



-- Update the currently configured value for xp_cmdshell



RECONFIGURE



GO



 



-- Disallow further advanced options to be changed.



EXEC sp_configure 'show advanced options', 0



GO



 



-- Update the currently configured value for advanced options.



RECONFIGURE



GO




Once successfully executed, the below script with the required changes could be executed to download the files -





--Replace NULL with keywords of the ReportManager's Report Path, 



--if reports from any specific path are to be downloaded



DECLARE @FilterReportPath AS VARCHAR(500) = NULL 



 



--Replace NULL with the keyword matching the Report File Name,



--if any specific reports are to be downloaded



DECLARE @FilterReportName AS VARCHAR(500) = NULL



 



--Replace this path with the Server Location where you want the



--reports to be downloaded..



DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\'



 



--Used to prepare the dynamic query



DECLARE @TSQL AS NVARCHAR(MAX)



 



--Reset the OutputPath separator.



SET @OutputPath = REPLACE(@OutputPath,'\','/')



 



--Simple validation of OutputPath; this can be changed as per ones need.



IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = ''



BEGIN



  SELECT 'Invalid Output Path'



END



ELSE



BEGIN



   --Prepare the query for download.



   /*



   Please note the following points -



   1. The BCP command could be modified as per ones need. E.g. Providing UserName/Password, etc.



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



   3. The BCP does not create missing Directories. So, additional logic could be implemented to handle that.



   4. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding. 



      It just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one. 



      However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF). 



      That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”. 



      While it is supported, it can cause problems with the conversion to XML, so it is removed.



   */



   SET @TSQL = STUFF((SELECT



                      ';EXEC master..xp_cmdshell ''bcp " ' +



                      ' SELECT ' +



                      ' CONVERT(VARCHAR(MAX), ' +



                      '       CASE ' +



                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+



                      '         ELSE C.Content '+



                      '       END) ' +



                      ' FROM ' +



                      ' [ReportServer].[dbo].[Catalog] CL ' +



                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +



                      ' WHERE ' +



                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x'''



                    FROM



                      [ReportServer].[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



END




Conclusion



Hope, this helps & save a lot of your valuable time.



Happy Reporting!

6 comments:

  1. Nice tip Hemant.
    This will help many.

    ReplyDelete
  2. Doesn't work. Don't bother.

    ReplyDelete
  3. This is great! Contrary to the Anonymous comment above, it DOES work. This will help my company so much!

    If anyone gets this error:

    Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file

    I was able to solve it by making sure the folder I was writing to already exists before I ran the SQL. The path is D:\Reports\Download\. Create those folders first, and you should be good to go!

    ReplyDelete
  4. is it possible to also include the folder structure? that would be great! I have about 400 reports

    ReplyDelete
  5. Nice work saved me a bunch of time but with a small change can also copy the directory structure.
    add these lines befoew the export
    DECLARE @MKDIRTSQL AS NVARCHAR(MAX)
    --select ';EXEC master..xp_cmdshell ''mkdir "c:/temp/ssrs' +path+'"''' from (select distinct path from [ReportServer].[dbo].[Catalog] CL WHERE CL.[Type] = 2 ) x
    SET @MKDIRTSQL = STUFF((select ';EXEC master..xp_cmdshell ''mkdir "'+ @OutputPath +path+'"''' from (select distinct path from [ReportServer].[dbo].[Catalog] CL WHERE CL.[Type] = 2 ) x FOR XML PATH('')), 1,1,'')
    EXEC SP_EXECUTESQL @MKDIRTSQL

    and then change path to @OutputPath + CL.[Path] + '/' + CL.Name.

    ReplyDelete
  6. Great stuff however I'm sad that the UTF thing messes with the content of my report because I'm loosing punctuations since my report is in French there's a lot of é è etc.... and those are all messed up !

    ReplyDelete

I appreciate your time, thanks for posting your comment. I will review and reply to your comment as soon as I can.

Thank you
Hemantgiri