Using an SSIS package makes it simple
| Downloads |
|---|
| 144506.zip |
If you're a DBA and your company uses SQL Server Reporting Services (SSRS), in some capacity you're probably also a report administrator. Because SSRS is a SQL Server technology, DBAs often are asked to manage SSRS just as they would manage SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server itself. I've been in this position. Outside of routine tasks such as deploying reports, setting permissions, and creating report subscriptions, I've been called on to answer questions about how certain reports are performing or how many times they're run and by whom. I've also been asked to discover which reports are no longer being used.
If you have one or two SSRS servers, you can easily answer such questions with a quick ad hoc query of the views or tables provided in the SSRS metadata database, ReportServer. But what if you have many SSRS servers? Further, what if you want to easily compare how one server might be faring against another server for performance load? Having this data centralized for proper analysis would be ideal. I'll show you how to use an SSIS package to pull catalog and execution log data from any number of SSRS instances into a central repository. I'll also provide some queries that you can use to answer fundamental questions about report statistics.
I'd like to point out that the technique I'll use in the SSIS package to retrieve data from each SSRS ReportServer database is a derivative of the DBA Repository solution. In this solution, I populate a package variable with a list of servers from an underlying table named serverlist_ssis in the DBA_Rep database. To take full advantage of the solution in this article, having the basic DBA_Rep database is required. The DBA Repository solution is simple to set up. I included two scripts and instructions in the ReadMe.txt file so that you don't have to wade through previous articles to begin collecting SSRS data. I also included a sample record and description in the ReadMe.txt file for the serverlist_ssis table to help you get started. You can download these files by clicking the Download button. If you want to learn more about the DBA Repository solution, check out the SQL Server Pro articles "DBA Repository 2010" (July 2010) and "Use SSRS and SSIS to Create a DBA Repository " (February 2008).
The Queries for the SSIS Package
Let's begin by looking at the queries that you can use to gather report server data and that you'll ultimately plug into the SSIS package to retrieve the data from every SSRS server. Depending on what version of SSRS you're currently running, the ReportServer database has tables, views, or both that contain report execution information. In SQL Server 2000 and SQL Server 2005, a table named ExecutionLog contains all the execution information. In SQL Server 2008, a view named ExecutionLog2 was added, and with SQL Server 2008 R2 came another view, ExecutionLog3. In SQL Server 2008 and later, ExecutionLog is still available as a view for backward compatibility. (It's also available as a table, which has been renamed to ExecutionLogStorage.) Although there's additional useful information in ExecutionLog2 and ExecutionLog3, most of the information of interest is available in ExecutionLog, so that's what this solution uses. Using ExecutionLog has an added benefit: If you have a mix of SQL Server versions across your SSRS infrastructure, you don't have to write multiple queries and objects to accommodate the different column names and data types across all the versions.
The ExecutionLog columns of interest include InstanceName (what report server processed the report), ReportID, UserName, RequestType, Format, Parameters, TimeStart, TimeEnd, TimeDataRetrieved, TimeProcessing, TimeRendering, Source (e.g., live, cache, session), Status (e.g., success, abort), ByteCount, and RowCount. Most of the columns are self-explanatory, but I'll expand on a few of them later in the article.
Listing 1 shows the query that you'll use to populate the DBA_Rep table in the repository database. Notice that there's a ReportID, which has a data type of uniqueidentifier. To make ReportID useful, you need to also collect the report names and the path to where the reports reside in the folder structure. To get that information, you need to pull data from the Catalog table in the ReportServer database. That table's ItemID column will be the joining key field to the ReportID field from ExecutionLog. Be warned that Microsoft doesn't condone this activity, so it's with a heavy heart that I point you to the query that I use to get the catalog data. Listing 2 shows this query.
There are a few things to note about this query:
- I added the server name to distinguish where the reports are coming from.
- I added a NOLOCK table hint to avoid any contention while the SSIS package is running.
- I included only the reports themselves, as indicated in the WHERE clause (Type = 2).
You can execute the queries in Listing 1 and Listing 2 against any ReportServer database, no matter what version of SSRS is installed. To consolidate the data, you can use an SSIS package that loops through all the report server databases and places the retrieved data into two local tables, which can be joined together through the ReportID and ItemID fields.
