How to deploy many reports to many SSRS Servers

Question

Does anybody know a tool that let me deploy many RDL files to many SSRS servers?

Up to 50 reports to more than 20 Servers. Every ssrs server has the same structure.

Thanks for your help

Hans

Answer 1

Good news, there are a several ways to do this, here is a list from low-level to high-level:

Create a custom application consuming SSRS Web Services

You can use Reporting Services web services to perform this task but I wouldn't recommend it unless you have specific needs which are not covered by other ways.
The reason is that it would take some time, and there is no need to reinvent the wheel.

There are 2 Web Services available, to manage Report Server items, you will need ReportingService2010 or ReportingService2005 depending of your Reporting Service instance.

More details on how to consume these web services here.

---

Manually Script

You could write scripts (.rss) and import them with RS.exe.
This tool is based on the Web Services mentioned earlier.

The rs.exe utility processes script that you provide in an input file. Use this utility to automate report server deployment and administration tasks.

Note that Sharepoint mode is supported with SQL Server 2008 R2+.

Here is a MSDN article explaining that gives more details:
Sample Reporting Services rs.exe Script to Migrate Content between Report Servers.

---

Use a tool to generate scripts

This one is my favorite, several tools have been written to script items from a Server A and restore it to Server B, one of them is RSScripter.
You can find a tutorial on how to use it here.

Internally it uses RS.exe, the steps with the GUI are:

1. Launch the RSScripter executable
2. Configure SSRS instance(s) in the Options
3. Click on "Get Catalog" to display all the items of the instance
4. Select the items you want to script (Reports, DataSources, Policies, ...)
5. Click on "Script", this will generate a folder containing the selected resources (rdl, rss, ...), and a batch file
6. Edit the batch file with your new instance details
7. If applicable, move the directory to a place where your server is reachable
8. Run the batch file and wait for completion. This can take time if you have a lot of reports, you can see the progress in the log file.

There is also the Reporting Service Migration Tool from Microsoft, which seems to have some additional features, but I never tested it.

Answer 2

You could write your own program to do this, like below:

        private static string UploadReportFiles(string[] args)
    {
        string results = String.Empty;
        string filesFolder = String.Empty;
        string fileName = String.Empty;
        if (args.Count() > 14)
        {
            try
            {
                //args[0] - Command
                //args[1] - Switch Should be /S Server Name
                string serverName = args[2];
                //args[3] - Switch Should be /U User Name
                string userName = args[4];
                //args[5] - Switch Should be /P User Password
                string password = args[6];
                //args[7] - Switch Should be /M Server Manager Folder
                string managerFolder = args[8];
                //args[9] - Switch Should be /R Server Reports Folder
                string reportFolder = args[10];
                //args[11] - Switch Should be /D Server Data Sources Folder
                string dataSourceFolder = args[12];
                //args[13] - Switch Should be /F Files Folder
                filesFolder = args[14];

                ReportingService2005 rs = new ReportingService2005();

                string url = <ProjectNamespace>.Properties.Settings.Default.RDLManager_ReportService_ReportingService2005.Replace("localhost", serverName).Replace("ReportServer", managerFolder);

                rs.Url = url;

                NetworkCredential networkCredential = new NetworkCredential(userName, password);
                rs.Credentials = networkCredential;

                CatalogItem[] rootItems = rs.ListChildren("/", false);

                IEnumerable<string> rootNames = from x in rootItems.AsEnumerable()
                                                select x.Name;

                if (!rootNames.Contains(reportFolder))
                {
                    rs.CreateFolder(reportFolder, "/", null);
                }

                CatalogItem[] items = rs.ListChildren("/" + reportFolder, false);

                IEnumerable<string> itemNames = from x in items.AsEnumerable()
                                                select x.Name;

                string[] reportFiles = Directory.GetFiles(filesFolder);
                foreach (string file in reportFiles)
                {
                    string returnMessage = String.Empty;
                    try
                    {
                        if (file.EndsWith(".rdl") && !file.Contains("Backup"))
                        {
                            FileInfo fi = new FileInfo(file);
                            fileName = fi.Name.Replace(".rdl", "");
                            System.Xml.XmlDocument doc = new System.Xml.XmlDocument();                               

                            string reportFilePath = Path.Combine(filesFolder, fileName);
                            using (FileStream fs = new FileStream(reportFilePath + ".rdl", FileMode.Open))
                            {
                                // Read the source file into a byte array.
                                byte[] bytes = new byte[fs.Length];
                                int numBytesToRead = (int)fs.Length;
                                int numBytesRead = 0;
                                while (numBytesToRead > 0)
                                {
                                    // Read may return anything from 0 to numBytesToRead.
                                    int n = fs.Read(bytes, numBytesRead, numBytesToRead);

                                    // Break when the end of the file is reached.
                                    if (n == 0)
                                        break;

                                    numBytesRead += n;
                                    numBytesToRead -= n;
                                }
                                numBytesToRead = bytes.Length;

                                string reportName = fileName.Replace(".rdl", "");

                                Warning[] publishErrors;

                                if (itemNames.Contains(reportName))
                                {
                                    System.Console.WriteLine("Uploading Report File - " + fileName);

                                    string report = "/" + reportFolder + "/" + reportName;
                                    DataSource[] existingDataSources = null;
                                    DataSource existingDataSource = null;
                                    existingDataSources = rs.GetItemDataSources(report);
                                    if (null != existingDataSources)
                                    {
                                        existingDataSource = existingDataSources[0];
                                    }

                                    publishErrors = rs.SetReportDefinition(report, bytes);

                                    DataSource[] publishedDataSources = null;
                                    publishedDataSources = rs.GetItemDataSources(report);

                                    if (null != existingDataSource)
                                    {
                                        List<DataSource> replacementDataSources = new List<DataSource>();
                                        DataSourceReference dsr = new DataSourceReference();
                                        string dataSourcePath = "/" + dataSourceFolder + "/" + existingDataSource.Name;
                                        dsr.Reference = dataSourcePath;

                                        foreach (DataSource publishedDataSource in publishedDataSources)
                                        {
                                            DataSource ds = new DataSource();
                                            ds.Item = (DataSourceDefinitionOrReference)dsr;
                                            ds.Name = publishedDataSource.Name;
                                            replacementDataSources.Add(ds);
                                        }

                                        rs.SetItemDataSources(report, replacementDataSources.ToArray());
                                    }
                                }
                                else
                                {
                                    publishErrors = rs.CreateReport(reportName, "/" + reportFolder, true, bytes, null);
                                    fileName = fileName + " - NEW REPORT UPDATE DATASOURCE MANUALLY!";
                                    System.Console.WriteLine("Uploading Report File - " + fileName);
                                }


                                if (null != publishErrors)
                                {

                                    foreach (Warning w in publishErrors)
                                    {
                                        if (w.Severity != "Warning")
                                        {
                                            returnMessage = "Warning: ";
                                            returnMessage = returnMessage + w.Message;
                                        }
                                    }
                                }                                   
                            }
                        }
                    }
                    catch (SoapException ex)
                    {
                        returnMessage = "Error Uploading File:" + fileName + " Exception:" + ex.Detail.InnerXml.ToString();
                    }
                    catch (IOException ex)
                    {
                        returnMessage = "Error Uploading File:" + fileName + " Exception:" + ex.Message;
                    }
                    catch (Exception ex)
                    {
                        returnMessage = "Error Uploading File:" + fileName + " Exception:" + ex.Message;
                    }

                    string logLineItem = "Uploaded " + fileName + " to " + serverName + ". " + returnMessage;
                    LogItem.LogMessage(logLineItem, filesFolder);

                }
                results = "Upload Complete!";
            }
            catch (SoapException ex)
            {
                results = "Error Uploading Files Exception:" + ex.Detail.InnerXml.ToString();
            }
            catch (IOException ex)
            {
                results = "Error Uploading Files Exception:" + ex.Message;
            }
            catch (Exception ex)
            {
                results = "Error Uploading Files Exception:" + ex.Message;
            }
        }
        else
        {
            ShowHelpText();
        }
        if (!String.IsNullOrEmpty(filesFolder))
        {
            LogItem.LogMessage(results, filesFolder);
        }
        return results;
    }