Migrating from Native to Integrated Mode SSRS Part 2: Essentials for Your Toolbox

Once you’ve made the decision to migrate from native mode to integrated mode, you need ways to assess what you’re up against. You need to know the current state of your native mode instance, and what that means for your migration, mainly:

• What reports are actually being used?
• How many email and file drop subscriptions are actively running?
• What data sources are needed to support the reports?

What Reports are Actually Being Used?

As you may recall from Part 1, the migration process is incredibly manual. There is no point in wasting time migrating and classifying reports that aren’t getting run anymore. Enter the incredibly handy information available in the ReportServer database’s ExecutionLog3 view. By default, SSRS instances keep the execution information of each report for 60 days, though this can be increased as needed. You may query the execution information a number of ways, but the ExecutionLog3 view makes the process much easier. For more information on the ExecutionLog views, see this article from Robert Bruckner  – it’s a few years old, but the principles still hold true.

Here is a basic query that you can use to search the execution information for the records associated with a particular path, such as the “/Accounts Receivable/” top level folder or something more granular, such as “/Sales/North America/Midwest/”.

SELECT [ItemPath] as ReportPath
,MAX([TimeStart]) as LastExecutedDate
,UserName
,Count([ItemPath]) as ExecutionCount
FROM [dbo].[ExecutionLog3]
WHERE ItemPath like '%' + @pFolderName + '%'
GROUP BY [ItemPath], UserName

Knowing that some reports may be very important only a few times a year, it’s also important to get a list of the reports that don’t have current execution information. You can review these potentially unused reports with your customers to identify which may still need to be migrated for their use in annual or other infrequent processes.

SELECT c.Path as UnusedReportPath
,c.Name as UnusedReportName
,u.UserName as LastModifiedBy
,c.ModifiedDate
FROM dbo.Catalog c
INNER JOIN dbo.users u
ON c.ModifiedByID = u.UserID
WHERE c.Type = 2
AND c.Path like '%' + @pFolderName + '%'
AND c.itemID NOT IN
(SELECT ItemID FROM dbo.ExecutionLogStorage x WHERE c.ItemID = x.ReportID)

I highly recommend that you create an SSRS report using these datasets, as depending on the size of your native mode instance, you will need to return to these queries again and again.

• Parameterize the folder name to allow flexible results.
• Make sure your SSRS report is designed in a way that prints and/or exports to Excel cleanly. You will need to share these results with the primary user contact(s) for each main report folder, and results that print or readily export to Excel will be much more approachable
• Bonus points if you leverage the Tablix PageName property to export the table of used reports to a different Excel tab from the unused reports. This helps delineate which reports are more likely to get migrated from those that are out of scope for migration.
• Optional: add a parameter that allows the user to choose whether to group by UserName or Report Path. This allows you to highlight either which reports are the most used or which users are the most active within that path. With two tablixes based on the same ExecutionLog3 dataset, you can set the visibility for each Tablix based on whether they’ve chosen to group by User or by Report Path

People can get a little tetchy when you ask them to choose what reports they really need. If your migration is ever going to be done, though, you need to migrate only those with execution log records or other current use evidence. If you have your reports checked into source control, you have a ready archive of them that can help defuse the tension of “What if I decide in three months that I really need that other copy of the TPS Report?”

How Many Email & File Drop Subscriptions are Configured?

For examples of how to query subscription information, please see SSRS Subscription Administration Part 1 and Part 2.

You may also wish to create an additional SSRS report that allows users to find all the email subscriptions they receive across all folders. See the query below for reference, and be aware that you’re doing a wildcard search across a large field – not smart to search for all subscriptions with “.com” email addresses. This isn’t a pretty query, but it is functional.

SELECT rs.SubscriptionID as SubscriptionID
, c.name as ReportName
, c.path as ReportPath
, s.laststatus as SubscriptionStatus
, s.eventtype
, s.LastRunTime
, CAST(ExtensionSettings AS XML).value('(//ParameterValue[Name="Subject"]/Value)[1]','VARCHAR(1024)') AS [Subject]
, CAST(ExtensionSettings AS XML).value('(//ParameterValue[Name="TO"]/Value)[1]','VARCHAR(1024)') AS [SendTo]
, CAST(ExtensionSettings AS XML).value('(//ParameterValue[Name="CC"]/Value)[1]','VARCHAR(1024)') AS [SendCC]
, CAST(ExtensionSettings AS XML).value('(//ParameterValue[Name="BCC"]/Value)[1]','VARCHAR(1024)') AS [SendBCC]
, CAST(s.Parameters AS XML).value('(//ParameterValue/Value)[1]','varchar(max)') as Parameter1
, CAST(s.Parameters AS XML).value('(//ParameterValue/Value)[2]','varchar(max)') as Parameter2
FROM ReportServer.dbo.ReportSchedule rs
INNER JOIN ReportServer.dbo.Subscriptions s
ON rs.SubscriptionID = s.SubscriptionID
INNER JOIN ReportServer.dbo.Catalog c
ON s.report_oid = c.itemid
WHERE CONVERT(VARCHAR(MAX),s.ExtensionSettings) LIKE '%' + @pEmailAddress + '%'

Optional Extra: What Email Subscriptions is a particular user receiving?

This will return ONLY email subscriptions where the user’s email address is specifically listed in one of the subscription email fields. If the user is part of a group email distribution list, you’d have to search on the group’s address, not the users’. No fancy Active Directory lookups going on here. That said, it can help give a primary business user context for just how many subscriptions he or she is receiving across a number of different report folders. Since the parameter is using a LIKE search, it’s flexible … to a fault.

SELECT rs.SubscriptionID as SubscriptionID
, c.name as ReportName
, c.path as ReportPath
, s.laststatus as SubscriptionStatus
, s.eventtype
, s.LastRunTime
, CAST(ExtensionSettings AS XML).value(‘(//ParameterValue[Name=”Subject”]/Value)[1]’,’VARCHAR(1024)’) AS [Subject]
, CAST(ExtensionSettings AS XML).value(‘(//ParameterValue[Name=”TO”]/Value)[1]’,’VARCHAR(1024)’) AS [SendTo]
, CAST(ExtensionSettings AS XML).value(‘(//ParameterValue[Name=”CC”]/Value)[1]’,’VARCHAR(1024)’) AS [SendCC]
, CAST(ExtensionSettings AS XML).value(‘(//ParameterValue[Name=”BCC”]/Value)[1]’,’VARCHAR(1024)’) AS [SendBCC]
, CAST(s.Parameters AS XML).value(‘(//ParameterValue/Value)[1]’,’varchar(max)’) as Parameter1
, CAST(s.Parameters AS XML).value(‘(//ParameterValue/Value)[2]’,’varchar(max)’) as Parameter2
FROM ReportServer.dbo.ReportSchedule rs
INNER JOIN ReportServer.dbo.Subscriptions s
ON rs.SubscriptionID = s.SubscriptionID
INNER JOIN ReportServer.dbo.Catalog c
ON s.report_oid = c.itemid
WHERE CONVERT(VARCHAR(MAX),s.ExtensionSettings) LIKE ‘%’ + @pEmailAddress + ‘%’

The list of configured subscriptions is an important part of the discussion with your primary report contacts.

• Which of these subscriptions are still necessary, and which are just annoying and can be deleted?
• If a number of them are running at nearly the same time, should they be configured on a shared schedule to make them easier to manage?
• If there’s a nest of file drop subscriptions that look like they’re creating a poor man’s data warehouse/archive, you may want to discuss more robust options with your users. Keeping a snapshot of the daily sales figures in little Excel workbooks out on a network share isn’t the best way to address enterprise BI needs. J

I learned a new respect for SSRS subscriptions throughout the course of the migration to integrated mode. Subscriptions aren’t just a matter of convenience, though it is awfully handy to have the status of something plopped into your inbox to remind you of a task or the status of a process. Subscriptions can also be an essential part of making sure the business is performing correctly. For example, check every 30 minutes for shipments that haven’t left the warehouse and are in danger of being late. Data driven subscriptions can be leveraged to only send a “nag” email when the subscription’s query returns results; only escalate this to the master data managers when a data mismatch is found.

I still maintain that subscription administration is given far too little love by Microsoft, but they’re an important part of SSRS functionality.

What Data Sources Are Needed to Support the Reports?

As we noted in Part 1, shared data sources are an essential part of a well-designed SSRS implementation, whether it be native mode or integrated mode. You will need to set up your shared data sources in a library that’s accessible by all of your report users, so it should be hosted on a site public to all employees. The library contents themselves can be made read-only for everyone but the administrators.

Here’s a query to use in your report for tracking down data source dependencies for a folder:

SELECT
DSC.Path as DataSourcePath,
DSC.Name as DataSourceName,
C.Name as ReportName,
C.Path as ReportPath,
C.ModifiedDate,
MU.UserName as ModifiedBy
FROM
dbo.Catalog AS C
INNER JOIN dbo.Users AS MU ON C.ModifiedByID = MU.UserID
INNER JOIN dbo.DataSource AS DS ON C.ItemID = DS.ItemID
INNER JOIN dbo.Catalog DSc ON DS.Link = DSc.ItemID
WHERE
c.path like '%' + @pFolderName + '%'
ORDER BY DSC.Name

Note that only data sources that will be common across multiple departments, such as the ERP system database or the enterprise data warehouse, need to be housed in a shared location. If you have reports against a system that’s proprietary to a particular department, you can create the data source objects within that department’s reports library.

I found that as the migration progressed, I used the datas source dependencies report less, as the common set of data sources had already been created. However, it helps you get a sense of the scope of each folder’s reports, and might help you determine if their department/audience base is wider than previously realized.

Next up – Part 3: Configuring Reporting Services in SharePoint Integrated Mode