Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Downloading Reports from an SSRS Server

RSS
Modified on Wed, Feb 16, 2022, 11:27 AM by Administrator Categorized as SSRS (SQL Server Reporting Services)

Overview

This article provides a means of downloading all the reports from an SSRS by way of a C# Console Application.

Prerequisites

  • You must have credentials to the SSRS (i.e., the "ReportServer") database with at least read access
  • The IP address where this code runs must be whitelisted (or at least not blocked by a firewall) on the SSRS server.

Code

Program.cs

class Program
{
    static void Main(string[] args)
    {
        var asm = Assembly.GetEntryAssembly();

        var pathItems = Path.GetDirectoryName(asm.Location).Split('\\');

        var rootFolder = Path.Combine(string.Join('\\', pathItems.Take(pathItems.Length - 3)), "Reports");

        var ssrs = new SsrsEngine();

        ssrs.DownloadReports(rootFolder);
    }
}

SsrsEngine.cs

/*
    Packages Required
    (1) Microsoft.Extensions.Configuration.Json
    (2) System.Data.SqlClient
    */
using Microsoft.Extensions.Configuration;
using System.Data.SqlClient;
using System.IO;

public class SsrsEngine
{
    public void DownloadReports(string rootFolder)
    {
        var config = GetConfiguration();

        var cs = config.GetConnectionString("SsrsCatalog");

        var dbServer = GetConnectionStringServerName(cs);

        using (var conn = new SqlConnection(cs))
        {
            conn.Open();

            var sql = @"
                    select 
                            SsrsPath = [Path]  
                        ,RdlContent = convert(varchar(max), content)
                    from 
                        dbo.[Catalog]
                    where 1=1
                        and [Type] = 2";

            using (var cmd = new SqlCommand(sql, conn))
            {
                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    var ssrsPath = reader.GetString(0);

                    if (ssrsPath.Length > 0)
                    {
                        ssrsPath = ssrsPath.Substring(1).Replace("/", "\\");
                    }

                    var rdlContent = reader.GetString(1);

                    /* The first three bytes of the content seem to be garbage. */
                    var searchFor = "<?xml";

                    var pos = rdlContent.IndexOf(searchFor);

                    if (pos > 0)
                    {
                        rdlContent = rdlContent.Substring(pos);
                    }

                    var absPath = Path.Combine(rootFolder, dbServer, ssrsPath + ".rdl");

                    var folder = Path.GetDirectoryName(absPath);

                    if (!Directory.Exists(folder))
                    {
                        Directory.CreateDirectory(folder);
                    }

                    File.WriteAllText(absPath, rdlContent);
                }
            }
        }
    }


    static IConfiguration GetConfiguration()
    {
        var currDir = Directory.GetCurrentDirectory();

        IConfigurationRoot config = new ConfigurationBuilder()
            .SetBasePath(currDir)
            .AddJsonFile("appsettings.json")
            .Build();

        return config;
    }



    static string GetConnectionStringServerName(string cs)
    {
        var csb = new SqlConnectionStringBuilder(cs);

        return csb.DataSource;
    }
}

appsettings.json

TODO: Fill in your server name and credentials in this database connection string.

{
  "ConnectionStrings": {
    "SsrsCatalog": "Server=ServerNameOrIpAddress;Initial Catalog=ReportServer;Persist Security Info=False;User ID=DbUsername;Password=REDACTED;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;"
  }
}

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2022, Patrick Jasinski.