SSRS Export part 2 (Export Data Source)

Back in August, I published a post on exporting SSRS report files en masse.  That article (which can be read here) detailed an SSIS package I created to export the files.  As it is published, it is only good for exporting the actual report files and not the data sources.

I knew of this short coming for some time and updated my SSIS package shortly after with the expectation of writing an update to that article.  Well, time went by a little too quickly.  Now it has been almost four months and I am finally getting to that update.  I am doing this all while also working out a TSQL only solution to do the same thing.  I hope to have that worked out soon with how to do it being published shortly after.

So, in keeping with the same format as the previous article, let’s start by detailing out the variables that have been added.

FileExportExtension – As the variable name should imply, this is the extension of the xml file that is to be created.  RDL would be for the report file, and RDS would be the data source (as examples).

Then inside the script task we will find the next change to be made.  The new variable we created will need to be added to the readonly variable list as shown.

So far so good.  The changes are simple and straight forward.

The next change is to the script.  Let’s click the edit script button and we can change the Main with the following.

Public Sub Main()
'
' Add your code here
'
        My.Computer.FileSystem.WriteAllText(Dts.Variables("ReportExportPath").Value + Dts.Variables("ReportName").Value + "." + Dts.Variables("FileExportExtension").Value, Dts.Variables("ReportXML").Value.ToString, False)
        Dts.TaskResult = ScriptResults.Success
    End Sub

Looking at this code, you will see once again that variable that we added popping up.

One key to this working effectively is the use of the ReportSourcePath variable.  An appropriate path must be specified that contains Data Sources in the Catalog table.  An example would be /Data Sources/.  Some environments may have a subfolder after the data sources.  Just make sure that the path specified leads to the data sources you want to export.

I had also considered altering the “Execute SQL Task” that starts the flow to this package.  The script there could be altered such that another variable may be added to designate report part type.

SELECT 
     ItemID,Name,[Type] 
   ,CASE Type 
      WHEN 2 THEN 'Report' 
      WHEN 5 THEN 'Data Source' 
      WHEN 7 THEN 'Report Part' 
      WHEN 8 THEN 'Shared Dataset' 
      ELSE 'Other' 
     END AS TypeDescription 
   ,CONVERT(varbinary(max),Content) AS Content    
   FROM ReportServer.dbo.Catalog 
   WHERE Type IN (2,5,8) 
And left(Path,len(@ReportPath)) = @ReportPath

The change would add another variable into this query in the where clause.  Change the type from in to an equality.  Add a variable that would designate the different types listed in the case statement – and it becomes pretty straight forward.  This change would allow more flexibility.  I will update at a later time with the changes I have made to the package to permit that.  But for now, I felt it more of a bonus addition and didn’t need it quite yet.  (Necessity drives functionality, right?)

If you make these suggested changes, you will have more flexibility in being able to export the various files related to reporting.  If you have played with Report Manager, you will know that there is no way to export an RDS file.  Now, you have a means to export the xml into a usable file that can be imported to another server – if you need it.

Check back in the future for that update to do this using TSQL as well as for the update to provide more flexibility to the package.