SSRS Migration: Data Sources Supported in Reporting Services 2012/2014

Migrating your Reporting Services instances to SQL Server 2012/2014 has a few key considerations.

First, the oldest version you can upgrade from is Reporting Services 2005 and that must be patched to SP4 (9.00.5000).

Next, any SQL Server 2005 report data sources are still supported in SQL Server 2012/2014. Here are the full list of data sources supported in SQL Server 2014 http://msdn.microsoft.com/en-us/library/ms159219.aspx. However your Report Server databases themselves can’t be hosted on SQL Server 2005 database instances http://support.microsoft.com/kb/2796721/en-nz.

Importantly, those pesky Report Models are depreciated in Reporting Services 2012/2014 http://msdn.microsoft.com/en-us/library/ms143509.aspx. They will migrate but you won’t be able to make changes after the migration – so at some point they will need to have their datasets rewritten. Getting a handle on how many reports hang off Report Models and giving your customers some options is a good way to manage this issue.

Finally, the full migration process is outlined here step by step http://msdn.microsoft.com/en-us/library/ms143724.aspx. There are risks in any major version migration, but running side by side environments and getting a business acceptance sign-off is the best way to mitigate those risks.

SSIS: Execution History Catalog Dashboard To Go

The SSISDB contains a wealth of statistical data for your SSIS package executions and messages.   In a package failure scenario, trawling through SQL Management Studio to get detailed reports becomes quite tedious.  For a very quick and easy dashboard looking at the most recent events try a simple Reporting Services report with two tables and two datasets.  The two datasets are outlined in more detail below.

Screen Shot 2014-08-07 at 10.23.02 pm

The first dataset takes the last 3 days worth of execution history for your packages as per the top table. Whilst this dataset returns one row per execution as displayed, adjusting the groupings in this query will give you interesting perspectives on Average Duration and Execution Count.

The second table contains the last 15 messages.  In a failure scenario the second dataset will typically hold the most recent failure if no other packages have been run subsequent to the failure.  However you may need to adjust the dataset to suit, where TOP is specified, or parameterise as required – add a little Dijon mustard 🙂

Execution Summary – Last 3 Days

SELECT        folder_name, COUNT(*) AS tally_executions, SUM(DATEDIFF(ms, start_time, end_time))/1000 AS total_duration_s, package_name,
                         CASE status WHEN 1 THEN 'Created' WHEN 2 THEN 'Running' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'Failed' WHEN 5 THEN 'Pending' WHEN 6 THEN 'Ended Unexpectedly'
                          WHEN 7 THEN 'Succeeded' WHEN 8 THEN 'Stopping' WHEN 9 THEN 'Completed' END AS Status, start_time
FROM            catalog.executions AS e
WHERE start_time > getdate()-3
GROUP BY folder_name, package_name, status, start_time
ORDER BY start_time DESC

Message Detail – Last 15 Messages

SELECT        TOP (15) event_message_id, message, package_name, event_name, message_source_name
FROM            (SELECT        event_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name,
                                                    event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID,
                                                    message_code
                          FROM            catalog.event_messages AS em
                          WHERE        (operation_id =
                                                        (SELECT        MAX(execution_id) AS Expr1
                                                          FROM            catalog.executions)) AND (event_name NOT LIKE '%Validate%')) AS q
ORDER BY message_time DESC​

Here’s a couple of chilly reads that go a little deeper.

http://sqlblog.com/blogs/jamie_thomson/archive/2012/10/17/querying-the-ssis-catalog-here-s-a-handy-query.aspx

http://www.rad.pasfu.com/index.php?/archives/79-SSIS-Catalog-Part-6-Operations.html

SSIS: Task failed because sgen.exe was not found, or the correct Microsoft Windows SDK is not installed.

You may have come across this error if you try to program against web services in your SSIS packages.

“Task failed because sgen.exe was not found, or the correct Microsoft Windows SDK is not installed”.   If your I.T. Department won’t let you install the SDK and you are out of Freddo frogs, follow these instructions to navigate to and disable the Generate Serialization Assembly option.

1. Open SQL Server Business Intelligence Development Studio or SQL Server Data Tools.
2. Open the project file or the solution file.
3. In Solution Explorer, double-click the SSIS Package to open the Package Designer.
4. Double-click Script Task in the Package Designer to open the Script Task Editor dialog box – Click Edit Script.

ScriptTask

6. In Project Explorer, right-click the project, and then click Properties.

ProjectProperties
7. Click the Build tab. In the Output area, select Off in the Generate serialization assembly list.

BuildTab
8. Make sure you Save Selected Items before you exit.

SaveSelectedItems

 

9. You should now be able to Build your code without error.