3 Jun 2017

SQL Server Reporting Services : Migration of reports between Report Manager

Hello Folks !!

This post is another extension of the previous post SQL Server Reporting Service : Multiple reports deletion from Report Manager at One Go, previous post detailed out how to remove multiple reports from the Report Manager using RSS file which subsequently using VB-Script, in this very much post I will explore on how to migrate all the reports or number of reports from one server to another or even on multiple server.

Again, the basic understanding why such requirement ever exists is just because now a days, organization suppose to upgrade their environment to meet the trending pace of Today's world in terms of technology, one such example is when organization decided to move from SQL Server 2012 to say SQL server 2019, in such a scenario, they supposed to move their subsequent reports those are residing in SQL Server 2012 Report Manager to SQL Server 2019 Report Manager, hence need of migration comes into light, again if one has to do it manually, it will be very monotonous, frustrating, time taking , tedious and cumbersome task to perform, hence we again need some advanced or optimised solution that will do the job for developer without wasting so much of developer time which they can utilise in another productive and important work.

Let's explore the possibility of optimised solution for such kind of client request, let's discuss in detail.

Let's start the road roller with some illustration.

Requirement :

Let's say you are working in XYZ organization and as a process of optimisation, organization decided to move their all of their database of existing SQL server to advance or new version of SQL Server, i.e. from SQL Server 2012 to SQL Server 2019, in order to do that, one has to move all of their databases, SQL Server Integration Services (SSIS) ETL packages, Report definition language (RDL) files of SQL Server Reporting service (SSRS) and other required objects to new SQL Server, let's say, client has raised a request for you to migrate all of previous SQL version reports hosted on SQL Server 2012 Report Manager to new SQL Server 2019 Report Manager, once you have this request and post exploration you comes to know that number of report are quite high in number, along with, there are quite a good management of reports and subsequent department reports placed at subsequent folder, plus there are folders for Dataset etc, requirement is to move all of them to new server.


Resolution : 

Again, this request of client is legitimate and billable but very tedious and cumbersome in nature, and literally no one wants to indulge into such kind of activity at least willingly. 

Nonetheless, very straight and simple solution is to move all the reports from source report manager to destination report manager one by one manually, but as a matter of fact, this can be done this way but requires more human efforts and time consumption is quite high, in another words, maximum input and limited output with probability of Human errors, I would never recommended such type of human intervention to deal with one object at a time and high tendency of mistakes and errors, not even in my dreams, so question is how to achieve this tedious goal, be optimistic, we have better solution which is detailed in further in this very post.

Before, jumping into the final solution, it is really  important to understand few concepts which are helpful in undersigning the solution

What is Report Manager ?

Report Manager is nothing but a repository which is available over the internet (or cloud) on which all reports will be hosted, user who having required access can only access the reports.

What is RSS Extension File?

RSS Extension file is what we call it is rich Summary file which is saved on XML kind of format, one can write code on the same and execute it via Command Prompt (CMD).

What is BAT Extension File?

BAT Extension file is script file which is used CMD to perform certain activity.

What is VB Script ?

VB Script is kind of scripting language where we can write a code to perform certain activity, it is same as C# Script with only difference is use of different programming language.

What is Command Prompt (CMD) ?

CMD is default command-line interpreter and also known as CMD.exe

Above provided definition is at very glance, if one want to understand or explore more on these topics, please explore on Google as this topic is out of scope for this post.

Hopefully, all the basic terminology that we are going to use in this solution are self explanatory and easy to understood, lets use these and move towards our goal.

Pre-Requisite 

1 : SQL Sever 
2 : RS.exe utility must be installed
3 : Destination folder must be available (if not available, must create) at destination report manager.
4 : SSRS_Migration.RSS - This is easily available at Microsoft site, one can have it form there.

Source Detail 
Server : ReportServer1
Report Folder : TEST

Destination Detail 
Server : ReportServer2
Report Folder TEST

To do 
To move all the reports of folder TEST on Report Manager of Production Server ReportServer1 to folder TEST on Report Manager of Development Server ReportServer2

Migration of reports from one Report Manager to another

Step 1 : Open Report Manager of ReportServer2

Step 2 : Check whether folder TEST is available of not, if not, please create folder named TEST. 

Step 3 : Open Notepad

Step 4 : Click on Save As  OR HIT < CTRL + SHIFT + S >  

Step 5 : Under Save As Type , select All Files

Step 6 : Provide File Name as MigrateReport.bat and write below code
D:\>D:\SSRS\MigrateReport.bat
D:\rs.exe -i ssrs_migration.rss -e Mgmt2010 -s "ReportServer1" -v f+"/TEST" -v ts="ReportServer" -v tf="/TEST"

* Below is the snapshot for the same



* Place at same location where ssrs_migration.rss is placed (Optional though, one can place any file at any location, placing at same place require less coding while calling files from CMD 

Step 7 : Open CMD command and execute MigrateReport.bat.

Hurray !!! Here we go, we got the desired result.


Pros and Cons

Pros 
1. This will help you to migrate all the reports from one Report Manager to another. 

Cons 
1. All the shared dataset which uses credentials will not be migrate to another server.

Author - Feel free to ask query if you have any query

9 Feb 2017

SQL Server Reporting Service : Multiple reports deletion from Report Manager at One Go

Hello Folks!!

This time I comes up with one of the very interesting topic of the SQL Server Reporting Services, this will allow user to get rid of the cumbersome task of deleting reports manually and in fact save lot of time that can be utilised in other more important productive work.

Now a days, every organization are indulging into migration of their deliverables from one location to another, sometime for better technology and sometime for removing all the obsolete objects from their environment, let's suppose your organization is also following the same path and on one very good morning of your work day, you will receive request from the client to remove all unwanted or obsolete reports that they have in our very own Report Manager, sounds good and affirmative but when you explored the details of the request then you get to know that the number of reports are quite high in number however your client provided you the list of all the reports along with the path or URL of all the reports those are resides on Report Manager.

Such kind of request are actually very time consuming and if one has to go with traditional way of deleting report one by one after navigating reports over the Report Manager, it will take ages to complete the process, its not only about time, it also quite frustrating, tedious and cumbersome task to deal with.

Let's explore the possibility of optimised solution for such kind of client request, let's discuss in detail.

Let's start the road roller with some illustration.

Requirement :

Let's say you are working in Sales organization and there are numerous reports residing on Report Manager of SQL Server Reporting Services, you get a JIRA request stating to delete OR remove all the reports from the Report Manager those are obsolete or Not in use any more (they have also provided list of report along with path of all the reports in Report Manager) reason being, client really do not want to waste its time, money and resources for those reports which are useless for them and they will not use it any time in future.

Resolution : 

Though, request is no doubt is very cumbersome and no one wants to work on such activity, but some one has to perform it as it is the legitimate request and client is paying for the same.

One could be solution is to delete all the reports one by one manually but for sure it will require more time and more human efforts, in addition, doing activity manually in itself is very cumbersome and tedious task to perform, so in ideal scenario, this must not be in the list of the solution, even, if it is, it must be last thing in the world to progress with.

So, lets move towards the actual solution of the request, but I would like to explain few things before elaborating the solution as these will be key part of the solution.

What is Report Manager ?

Report Manager is nothing but a repository which is available over the internet (or cloud) on which all reports will be hosted, user who having required access can only access the reports.

What is RSS Extension File?

RSS Extension file is what we call it is rich Summary file which is saved on XML kind of format, one can write code on the same and execute it via Command Prompt (CMD).

What is VB Script ?

VB Script is kind of scripting language where we can write a code to perform certain activity, it is same as C# Script with only difference is use of different programming language.

What is Command Prompt (CMD) ?

CMD is default command-line interpreter and also known as CMD.exe

Definition provided above for the objects are at glance, if you want to have more detail on the subject you can refer them in detail over the internet.

As of now, you understood well all the basic terminology that are going to use in this very subject, lets start with the solution.

One of the handful and most useful method to delete multiple reports is to use RSS file for the deletion of reports, herein, I am detailing out all the steps that needs to perform this activity.

Delete one Report at a time

Step 1 : Open Notepad

Step 2 : Click on Save As  OR HIT < CTRL + SHIFT + S >  

Step 3 : Under Save As Type , select All Files

Step 4 : Provide File Name as TestDelete.rss

Step 5 : Write below VB - Script  inside the file  
    Public Sub Main()
        rs.DeleteItem("/Report_test/TestReport/")
    End Sub

* Below is the snapshot of the .RSS file that was created in above steps.


Step 6 : Place TestDelete.rss at any location where ever your organization has or you want.

Note - /Report_Test/TestReport : Root Folder in Report Manager, i.e. in this case, we have folder named Report_test at root on Report Manager and TestReport.rdl  is RDL file inside that folder.

Step 7 : Herein, the idea is to call TestDelete.rss from CMD (Command Prompt)Now, with the help of CMD command, we can delete above aforementioned report, please find snapshot below.


Hurray !!! Here we go, we got the desired result.

Required report got deleted from the Report Manager as soon as we have executed above CMD.

Delete multiple Reports at a time

One might thinks, aforementioned procedure can only delete one report at a time, then what is the difference between deleting everything manually one by one and using above procedure and providing report name one by one and deleting report, so Yes, you are absolutely correct, literally, they are more or less same but later will take bit less time than former however level of frustration and monotonous work will remain the same.

Just to enhance and make this procedure helpful to remove multiple report at a matter of time, we need to do little change in the VB Script and then we will be able to delete multiple reports at one go.

Below are the complete steps to perform the activity.

Step 1 : Open Notepad

Step 2 : Click on Save As  OR HIT < CTRL + SHIFT + S >  

Step 3 : Under Save As Type , select All Files

Step 4 : Provide File Name as TestDelete.rss

Step 5 : Write below VB - Script inside the file  
    Public Sub Main()
        rs.DeleteItem("/Report_test/TestReport/")
        rs.DeleteItem("/Report_test/TestReport1/")
    End Sub

Note - Just to include one more row to the VB-Script, highlighting in RED above, if you want to delete n number of rows, you need to include all the report path one by one as shown in the Step-5, as we have all list of path of complete report, so it won't be time consuming task to accumulate all the report path , may be MS- Excel will help you in gather list of path of report in quick time.

* Below is the snapshot of the .RSS file that was created in above steps       


Step 6 : Place TestDelete.rss at any location where ever your organization has or you want.

Step 7 : Herein, the idea is to call TestDelete.rss from CMD (Command Prompt)Now, with the help of CMD command, we can delete above aforementioned report, please find snapshot below.


Hurray !!! Here we go, we got the desired result.

Pros and Cons

Pros 
1. This will help you to delete multiple reports at one and consume less time.

Cons 
1. VB Script and CMD knowledge is required to perform aforementioned activity.

Author - Feel free to ask query if you have any query