var msgnocomment = 'Enter a comment'; var txt_norequest = 'Can not complete the request.';
SSRS Reports – Mass update all reports once
Created by
Wednesday, April 27, 2016

Recently I have been working with the joy that is SSRS. I have much to say about SQL Server Reporting Services but that’s for another blog post. For now I want to discuss a problem I ran into and the resolution I found to overcome it.

 

SSRS is not and never claims to be an aesthetic technology. Many posts and discussions I stumble across have a lot of frustration with its front end aspects. More frustrations arise when you decide to bring it into an ASP.NET application using the report viewer control library. One of the main reasons for working on a solution to “mass update” many reports is that we i needed to change a lot of reports at once. One of our data sources with more than 60 reports needed to chance. I had to change all instances to allow us to move to a different server. Doing this one by one was to tedious of a task. Using this method it took minutes rather than hours having to go into each report and update.

ReportBuilder

 

I am currently working on a reporting project which contains well above 60 reports. You can imagine that it can be somewhat frustrating when there is a request to change small things like the font, shape, size, color etc.. of the “reports”. Unless you want to overlay CSS you will spend hours upon hours going through each individual report in either BIDS or the Report Builder Application. Quite mundane.

Rather than toil over the concept of Overlaying CSS I decided to investigate deeper. I will update this post and go through the in depth details of the report server database. For now though all you need to study and investigate is the [dbo.Catalog] within your ReportServer database. Each individual report in this table as Hex in the [Contents] column. Each report content is simple XML. That’s what reports really just are XML. Each report has its own [ItemID] and to distinguish between reports and other items report type 2. Below is a query you can run in SSMS to check out the table.

 

The program in simple terms goes into this table and extracts each individual report item content. The [Content] is stored as hex value so conversion is needed to put it into a human readable string. I then dump each of these reports in its raw XML format into individual text files within a folder titled the name of its [ItemID]. What you can do then is open each of these report XML text files in notepad ++ or sublime and find and replace all of what you are looking to change. For example if you want to maybe change the font of all your reports at once you could do as shown below.

findreplace

 

I wrote this console application as a help tool for myself in a few hours and so there are many bugs in it. However it is free to use and contribute to on Github if anyone would also find this a useful tool. I explain the basic setup involved in getting the application going on its GitHub read me. If there is interest in the future i will probably do a video demonstration of it in action.

 

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Serialization;
namespace SSRSUpdateReports
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connection Setup
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = "Data Source=[ReportServer]; Initial Catalog=ReportServer; Trusted_Connection=True;";
                conn.Open();
                if (conn.State != ConnectionState.Open)
                    Console.WriteLine("Connection to database failed!");
                else
                    Console.WriteLine("Connection to Database Success.");
                Console.WriteLine("");
                Console.WriteLine("Continue? (Y/N)");
                string continueAnswer = Console.ReadLine();
                if (continueAnswer == "N")
                {
                    // Quit the application
                    Environment.Exit(0);
                }
                //SSMS Query: SELECT ItemID,Name,Content,Type FROM Catalog WHERE Type = 2
                SqlCommand getReportCatalogContentCommand = new SqlCommand("SELECT ItemID,Name,Content,Type,Path FROM Catalog WHERE Type = 2", conn);
                getReportCatalogContentCommand.Parameters.Add(new SqlParameter("0", 1));
                using (SqlDataReader reader = getReportCatalogContentCommand.ExecuteReader())
                {
                    Console.WriteLine("SSRS Tool : Extract Report XML for all reports, edit and insert.");
                    while (reader.Read())
                    {
                        // Load your reports into a datatable
                        DataTable dt = new DataTable();
                        dt.Load(reader);
                        // For each report item write the UniqueId and Content
                        foreach (DataRow dr in dt.Rows)
                        {
                            Console.WriteLine("Item: " + dr[0] + "\t" + dr[1]);
                            // Pass that report item into conversion method
                            CreateFileConvertData(dr);
                            // Lets close our connection till we want to update our xml data
                            conn.Close();
                        }
                        Console.WriteLine("Your Reports were sucessfully decoded and stored in C:\\MyTmpXMLReports\\");
                        Console.WriteLine("--------------");
                        Console.WriteLine("You can now edit the XML in each report text file");
                        Console.WriteLine("--------------");
                        Console.WriteLine("Ready to insert your changes back to the Report Server? (Y/N)");
                        string answer = Console.ReadLine();
                        if (answer == "Y")
                        {
                            InsertXMLChangesBackIntoDB();
                        }
                        else
                            Console.WriteLine("Changes were not inserted into the ReportServer DB");
                        // Quit the application
                        Environment.Exit(0);
                        Console.ReadLine();
                        Console.Clear();
                        return;
                    }
                }
            }
        }
        // After XML changes have been made we save and insert those changes back into their respective positions in the report catalog
        private static void InsertXMLChangesBackIntoDB()
        {
            string path = @"C:\MyTmpXMLReports\";
            string[] reportName = Directory.GetFiles(path);
            // Go through each report item 
            foreach (string fileName in Directory.GetFiles(path).Select(Path.GetFileNameWithoutExtension))
            {
                Console.WriteLine(fileName);
                string uniqueIdentifier = fileName;
                string contents = System.IO.File.ReadAllText(path + fileName + ".txt");
                byte[] _currentXML = ConvertStringToByte(contents);
                using (SqlConnection conn = new SqlConnection())
                {
                    conn.ConnectionString = "Data Source=[ReportServer]; Initial Catalog=ReportServer; Trusted_Connection=True;";
                    using (SqlCommand command = conn.CreateCommand())
                    {
                        conn.Open();
                        command.Parameters.AddWithValue("@Content", _currentXML);
                        command.Parameters.AddWithValue("@ItemID", fileName);
                        command.CommandText = "UPDATE Catalog SET Content = @Content WHERE ItemID = @ItemID";
                        command.ExecuteNonQuery();
                    }
                    Console.WriteLine("Status: Update Query Ran Sucessfully.");
                    Console.WriteLine("");
                    conn.Close();
                }
                Array.Clear(_currentXML, 0, _currentXML.Length);
            }
            Console.WriteLine("SSRS Reports Update Successful!");
        }
        private static byte[] ConvertStringToByte(string Input)
        {
            return System.Text.Encoding.UTF8.GetBytes(Input);
        }
        public static void CreateFileConvertData(DataRow dr)
        {
            var _reportItemId = dr[0];
            var _reportName = dr[1];
            var _currentContent = dr[2];
            var _reportServerPath = dr[4];
            // Convert byte[] to string
            Byte[] _currentByteArrary = (byte[])dr[2];
            string output = ConvertByteArrayToString(_currentByteArrary);
            Console.WriteLine("");
            string path = @"C:\MyTmpXMLReports\";
            File.Create(path + _reportItemId + ".txt").Close();
            StreamWriter file = new StreamWriter(path + _reportItemId + ".txt");
            file.WriteLine(output);
            file.Close();
        }
        // Pass in the Byte Array and perform the conversion
        private static string ConvertByteArrayToString(Byte[] _currentByteArrary)
        {
            string StringOutput = System.Text.Encoding.UTF8.GetString(_currentByteArrary);
            return StringOutput;
        }
    }
}

GitHub – https://github.com/eoiner/SSRSUpdateReports

 

 

4 points
1 2 3 4 5

I like

You like

Report

You have reported
Reasons
Cancel
1 comment
Friday, May 13, 2016
Is there a way to do this by segregating out the report items by folder on the server? I have multiple folders for different departments and need to mass update only one.