SSIS: Package run time statistics in SSRS

Problem

In most companies developers are restricted from accessing the MSDB database and they rarely know the performance of their packages in a production environment unless they have access to third party software tools or a friendly DBA. This happened to me once when I wanted to know how long my packages ran in a production environment and I had no access to the MSDB database to look at the sysjobs and sysschedules tables. The work around is to enable SQL Server logging in SSIS packages and to create a SSRS report from the sysssislog table.

Solution

The logic behind this solution is to enable SQL Server Logging in SSIS packages while we create/develop the package and send it for deployment.

For this demo open any of your existing packages. I opened my Adventure Works import package which imports Sales, Customer details, Purchase Orders and Shipment details from my SQLCAST 2014 source database to my MSSQLTips destination database.  In this example, both databases are on different servers.

Open_an_existing_package

Next step is to enable Logging in the package by right clicking in the Designer and select Logging as shown below.

Open_Logging

Select “SSIS log provider for SQL Server” from the options provided and click Add.

Add_SQL_Server_Logging

In the configuration drop down, select the connection manager of the destination MSSQLTips database.

Add_the_Connection

When SQL Server Logging is enabled in a package, it creates a sysssislog table in the database during its first run and logs all the package related events in that table.

Here is the destination MSSQLTips database view from SQL Server Management Studio Object Explorer and we see there is not a table with the name syssislog before the package execution.

Object_Explorer_before_run

Let’s run the package by right clicking in the package and click Execute Package. The package runs and completes as shown below.

Run_the_Package

Let’s refresh the destination MSSQLTips database and we will have a new table sysssislog as shown below.

Object_Explorer_before_run

Open the sysssislog table and you will see the data like below.

sysssislog_table

Create SSRS Report

The below code is used to calculate the time taken for the package to run from the above table.

SELECT  COMPUTER as MACHINE_NAME
       ,OPERATOR AS LOGIN_NAME
	   ,SOURCE AS PACKAGE_NAME
	   ,MAX(DATACODE) AS DATA_CODE
	   ,MIN(STARTTIME) AS START_TIME
	   ,MAX(ENDTIME) AS END_TIME
	   ,DATEDIFF(MINUTE,MIN(STARTTIME),MAX(ENDTIME)) AS RUN_TIME
	   
FROM    SYSSSISLOG
WHERE        (EVENT IN ('PACKAGESTART', 'PACKAGEEND'))
GROUP BY COMPUTER, OPERATOR, SOURCE
ORDER BY SOURCE
GO

We will use the above code to create a SSRS report using these indicators.

According to MSDN specifications, the values returned in the datacode column of the sysssislog table gives the result of a package run and they are as follows. We will use these as indicators for the SSRS report.

  • Datacode = 0 represents Success
  • Datacode = 1 represents Failure
  • Datacode = 2 represents Cancelled

Create a new SSRS Report project and add a new Report Item and add connection to the MSSQLTips database at the data source.

Add a new dataset and select use the dataset embedded in my report option and connect to the data source created.

Copy and paste the above SQL query in the Query box of the dataset properties section as shown below:

sysssislog_table

Go to the toolbox then drag and drop a table.  Next map it to the columns of the dataset and keep the datacode column as the last column and rename the column header to Result.

Drag and drop the indicator from the toolbox to the DataCode column, it will open a box like below:

sysssislog_table

Select the above symbol and click OK.

Right click the datacode column and open the Indicator Properties.  Next go to the Values and States tab then  map the values of the data code as shown below and press the OK button.

sysssislog table

The indicator column changes like below. Format the column headers as per your convenience and the report looks like the below example in the designer:

sysssislog table

Run the report by right clicking at the Report Item level in the solution properties and you will see the report run as shown below.

sysssislog table
Next Steps

Extend the above logging process for all your packages and try creating the report to show a summary of all packages.

Check out more tips related to SSIS:

SSRS: Reporting Business Requirement Template

There comes a time in every developers career where you will receive change request after change request on what in the beginning seems like an “easy” report.  This can increase development time and create unnecessary tension and frustration between the end business user and the developer.  How about those times where you encounter scope creep?  Annoying right?

I have been put into a position to create business requirement documentation or a BRD for the reporting groups to help streamline the issues that we were facing in the above paragraph.

In general, I think the most important features are:

1. Report mock-up
This has been a LIFE SAVER!  Seriously….This should be the easiest piece for the business user slapping together the document.  Go into excel, create what you want the report to look like colors, layout, etc.  Paste it into the requirements document.  It alleviates any confusion for the developer on something as simple as the column headers or color schema.

2. Calculations
Believe it or not this has been a tough one to get the business user to understand.  Especially where I am now, they use different calculations for the same ‘meaning’ depending on the client.  This can become confusing and flat out make a data warehouse impossible to obtain…But on the flip side, having these all up front can help the developer determine if the SQL engine will be best utilized in the dataset or within the RDL itself.

3. Roles – Developers, Testing, Approvals
Seems like a no brainer, but you would not believe how many times I have had to go back to the requestor and ask who will be validating and testing their material.

I highly encourage you to take a look at the document I have attached.  It is the form I created and fine tune it to meet your organizations needs.

ss1 ss2 ss3 ss4
For a Requirements Document Template for an ETL Project

After enough trial and error from the best and worst clients, business analysts, executive sponsors, and my own shining and less-than-shining moments I have seen many developers confronted with poor requirements turn into … the DEV Nazi!!

The DEV Nazi

So to make sure that doesn’t happen to you, here’s a template for your reporting projects.

In scope for this article

  • A requirements doc template designed for business analysts to cover most reporting projects.
  • Witty advice

Out of scope for this article
(But maybe if you vote Yes and comment nicely at the bottom of this article I’ll write another article on it.)

  • Requirements Elicitation
  • Requirements documents specific to other types of projects, such as ETL and Data Warehousing
  • Report Design, especially the new minimalist style that’s gaining popularity

So here we go..

Version History
A ‘who changed what when’ chronology of all changes, either using Word change tracking or lines like ‘Bob’s changes in yellow highlight’.  Keeps everyone honest when there are lots of changes.

Purpose
Also known as project objective, business goals, business problem statement, and various other terms.
A simple ‘Here’s why we’re doing this’ paragraph.  The target audience being those that are likely to only read this paragraph, but this also gives the developer some design decision guidance.

In Scope / Out of Scope
Everybody LOVES this section!  Okay, developers LOVE this section.  In Scope is a summary of what’s in the requirements.  Out of Scope is usually a Top 10 list of things that are close but not in, and answers the often asked question ‘Are we also getting this too?’  This is a developer’s best line of defenseagainst scope creep by false or unstated expectations.

And yes, just because person x told person y a month ago that it’s in requirements, or this email two months ago said it’s in, or was mentioned on the golf course last year during preliminary negotiations means that it’s in.   I’ve also known more than a couple of clients that will negotiate effort, cost, and time, and then scope creep the hell out of a project in order to make themselves look better.  Been there, dealt with that.

grumpy-cat-out-of-scope.jpg
Schedule
A statement on when this can be completed, such as ‘When division X is in the data warehouse’, ‘When project ABC is completed’, ‘Beginning in FY 2013’, ‘Yesterday’, etc.  Helps project managers plan this project within a project management system that deals with all projects, all resources, and available time.

Report properties

  • Name – The name people will call it.
  • Description – 25 words or less.
  • Population – “All x’s by y for time period z-1 to z-2”.  Also note if report values are supposed to match any other report.
  • Business Owner(s) – The person that will approve the creation of the report, and likely any future changes.
  • Technical Owner(s) – The person that will build the report, and likely any future changes.
  • Is there a Service Level Agreement (SLA)? – This will dictate how much effort will be done to log report success/failure, does this warrant somebody getting a phone call at two in the morning to fix it, and how many resources should be thrown to break/fix it.
  • Style sheet / cosmetics to use – Usually in the form of ‘make this look like..’
  • Exact source of data if known, and if it matters.
  • Page count – Does it absolutely, positively have to fit on one page?
  • Does this report need to be historically reproducable? – If your data is frequently updated or deleted, re-running a report for a prior period may not result in the same exact report as when it was originally ran.
  • Availability – Once a day at 8am, 24/7, etc.
  • Accessibility – How will it be accessed?  Internally, externally, on a boat, with a goat…
  • User Security – Who can view the report.
  • Data Security – Does it contain Personal Health Information (PHI) or Personal Identity Information (PII) that would require protection?
  • Does anything get turned off once this report is placed in production?

Parameters

  • User supplied values that will change the population.  For each parameter
  • Name as it would appear on the screen
  • Format – Number, date, text, etc.  Also is it free-form text, or populated from a range of values?
  • Cascading – Are parameter 2 values dependent on what is selected in parameter 1?
  • Should an ‘All’ option be added?
  • Can users select multiple values?
  • User security – Does any of the above require users to have access to only a specific set of parameters, such as the Seattle boss can only run it for Seattle?
  • Time period – Will this report ever need to be run for a specific date or range of dates?

Header

  • Will usually be dictated by style.  Frequent header items are the company logo, formal name, report number if it exists, and any parameters that were used to generate the report.

Detail
For each section

  • How is it populated – Especially if sections are populated differently.
  • Drill down – Click or mouse over something and details either appear below it
  • Grouping, including header and footer info.
  • Sorting – Default sort order, which columns are sortable, and any special sort order other than 0-9 or A-Z.
  • Links to other reports – If there are multiple reports and links between them, a map is an excellent idea. Each ‘other report’ would require its own requirements doc.
  • Alternating background color – Makes the report easier to read.

For each column

  • Name
  • How populated – Single value, a defined calculation such as ‘Beginning inventory for the month + Purchases – Sales’

Fun fact:  Providing a PowerPoint presentation from some VP and saying ‘these numbers are just to illustrate the concept’ is not a good requirement for a calculated column. Trust me on this one.

  • Data Type – Numeric, Currency, Date, Text.  Also other formatting info such as decimal places, dollar sign, thousands separators, and negative number handling.
  • Borders – Around all cells, none, heavy borders on certain sections.
  • Conditional Formatting – Make the color of bad stuff red, good stuff green, etc.

Does any part of the report have to match other parts of the same report?  (i.e. Sum of Dollar Amount matches ‘Grand Total’ text box)

Footer

  • Will usually be dictated by style.  Frequent footer items include page number, date/time of generation, and a legal notice.

Execution
Automated Execution

  • Time period – Mmonthly, daily, immediately after another process completes
  • Delivery method – Email, Excel, etc.
  • Distribution List – Who gets the report?
  • Data Security – Does the report need to be encrypted/decrypted?

Manual Execution

  • Where does the user go to execute it?

Archival

  • Are reports archived – Where, and for how long…
  • If yes, is there a reason why – SOX, ISO 9000, CIA, Company Policy, etc.

Are requirements easily understood?
Any confusion in requirements is going to be defined differenty by different people, resulting in time and effort, and goodwill if that confusion is between you and the client.

unclear-business-requirements-or-scope-c
A big honkin’ list of other quantifiable things you may need to directly address
Availability, Capacity, Data Currency, Data Retention, Degradation, Deployability, Exception handling, Extensibility (flexibility), Internationalization, Interoperability, Audit logs, Maintainability, Portability, Privacy, Recoverability, Reliability, Response time, Scalability, Security, Upgradeability, Usability.

NOT to be confused with these MBA Buzzword Bingo words, which may sound real impressive but have no quantifiable characteristics whatsoever:

World-class, best of class, best of breed, industry leading, empowerment, collaboration, repurpose, frictionless, client-focused, ecosystem, excellence, synergize, geo-targeted, diverse, environment, core competency…

dilbert-buzzword-bingo.jpg

And finally :: drum roll ::  Top 10 tips for writing requirements docs…

10   If requirements documents were easy, they’d offshore both the requirements and development for a third of what you cost, so don’t get bent out of shape when things are complicated.  It’s job security.
9      Never underestimate the awesome power of a :: blank stare :: to get people to provide you with better requirements
8      Sometimes ‘draw me a sketch’ is an excellent place to start requirements elicitation.
7      Never assume customers know exactly what they want.  Sometimes you have to guide them to the answer.
“If I had asked people what they wanted, they would have said faster horses.” – Henry Ford
6     Scope Creep – Changes in requirements after the initial ones are approved, but there cannot be an expectation that they will be immediately accepted.  They must be managed with budget, schedule, and resources.
5      Make sure the right stakeholders are defined, included in requirements elicitation, and accountable.
4      You are not an order taker!  (see ‘Faster Horses’ above).  This means you have to own your expertise and discover your customers needs together
3      Being a Business Analyst is its own career track, with its own training and certification.  The IIBA has plenty of resources.
2      Requirements documents should be signed by all customers.  In blood would be preferable, but in modern times ink is an acceptable alternative. … and the number one tip for writing requirements docs…
1      Any requirement that is missed, and caught in later stages of development, will cost WAY more money to fix, so make sure you get it all!!

Thank you for reading my article, please leave valuable feedback. If you liked this article and would like to see more, please click the Good Article button near the bottom of this article.

SQL: Date functions, format, conversion

Get the start of the month:

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth

  1. It finds the difference of the date between the SQL calendar start date (1900-01-01) in month and
  2. then add that difference in the month to the SQL calendar  start date
  3. Another way:
    SELECT DATEADD(DAY,1,EOMONTH(@mydate,-1))

End of the month:

SELECT EOMONTH(@mydate) AS EndOfMonth

Validate a column if it is DATE

select ISDATE(dob) from eployee;

Find the name of the Day, Wednesday:

select datename(weekday, convert(datetime, @today)) as datename;

Regular date expression are entered in year, month, day format:

yyyymmdd: ‘20051128’ 2005 v 28th

Convert the date to datename, monthname (01 January 2016) format: 

The output format has to be varchar data type not as date datatype. Input when you convert to datetime; output when you convert to character data. Input will show how it will store the data in the SQL server, output will display the result to the user screen according to style format:

select convert(nvarchar, GETDATE(), 106) AS spdate;

select convert(varchar, convert(date, ’12/04/2016′, 103), 106) as spdate;
select convert(date, convert(date, ’12/04/2016′, 103), 106) as spdate;

select convert(varchar, cast(’12/04/2016′ as date), 106) as spdate;
select convert(date, cast(’12/04/2016′ as date), 6) as spdate;

Check if the date is the First Friday of the Month:

declare @today datetime2;
declare @isFirstFriday bit;
set @today=CONVERT(datetime2, ’06/05/2016′);
set @isFirstFriday=0;
set @isFirstFriday= ( CASE WHEN ((DATEPART(DAY, @today)<=7 and datename(weekday, @today)=’Friday’)) THEN 1
ELSE 0
END);
select @isFirstFriday as IsFirstFriday;
select datename(weekday, @today) as datename;
–Check if the date is First Friday of the Month
declare @isFirstFriday bit=0;
set @isFirstFriday= ( CASE WHEN ((DATEPART(DAY, GETDATE())<=7 and datename(weekday, GETDATE())=’Friday’)) THEN 1
ELSE 0
END);
select @isFirstFriday as IsFirstFriday;
select datename(weekday, GETDATE()) as datename;

Copy the time portion of a DATETIME2

Using SSIS package, when the datetime datatype is copied from from one sql table to another table, the year month and day are copied but the time component is set to 00:00:00.000. To copy the time portion as well use the data type datetimeoffset(n)

DECLARE @datetimeoffset datetimeoffset(4) = '1968-10-23 12:45:37.1234 +10:0';
DECLARE @datetime datetime = @datetimeoffset;

SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset';

SQL:
Default value of datetime2 column getdate() was setting the date but not the time component. It was setting time to 00:00:00.000

In that table in SQL Server, specify the default value of that column to be CURRENT_TIMESTAMP.

DataType of that column may be DateTime or datetime2.

C# Date format:

DateTime.Now.ToString(“yyyyMMdd_HHmmss”);

Find the name of the month or name of the day

DATENAME(weekday, GETDATE())

It returns Friday

Get the Week start date and week last  date:

@@DATEFIRST (Transact-SQL) SET DATEFIRST specifies the first day of the week. The U.S. English default is 7, Sunday.

To change the week begin day either change the language settings or

SET DATEFIRST 3.

DECLARE @today tinyint;
SELECT @today=DATEPART(WEEKDAY, GETDATE());
SELECT
GETDATE() AS TodayDate
,@@DATEFIRST AS FirstDayOfTheWeek
,DATEADD(DAY, – @today, GETDATE()) AS WeekStartDate
,DATENAME(WEEKDAY, DATEADD(DAY, – @today + 1, GETDATE())) AS WeekStartDay
/*
Say in your language settings Sunday is the week start day
Today is Thursday when you are running your query.
So from Sunday – Thursday the number of days is 5 including Sunday.
If we deduct 5 days from today it will return Saturday.
That is why, in order to get the correct Start date we have add deduct 1 day less
*/
,DATENAME(WEEKDAY, DATEADD(DAY, @@DATEFIRST- @today, GETDATE())) AS WeekEndDay
,@today AS ‘TodayNumberInThisWeek’

C# Format DateTime AS yyyyMMddHHmmss

DateTime.Now.ToString("yyyyMMddHHmmss"); // case sensitive

T-SQL Format DateTime AS yyyyMMddHHmmss

SELECT format(getdate(),’yyyyMMdd_HHmmss’)

SELECT format(getdate(),’D’, ‘en-gb’)
SELECT format(getdate(),’D’, ‘en-US’)

SSIS Format DateTime AS yyyyMMddHHmmss

Need to declare the variable as string. Get the current date by getdate() then convert the getdate() datepart value to string.

(DT_WSTR,4)YEAR(GETDATE()) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + " "
    + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)

Check if Date only then Convert otherwise use default Date–>convert to nvarchar, 112 format–>convert to INT

CONVERT(INT, CONVERT(nvarchar(10), (CASE WHEN ISDATE(DateOfBirth)=1 THEN CONVERT(DATE, DateOfBirth)

ELSE CONVERT(DATE, ‘1899-12-30’) END), 112)) AS DateOfBirth

input DOB DATE format is: nvarchar(255) – 31/07/1940

desired output: 19400731

CONVERT(NVARCHAR(10), CONVERT(DATE, DOB, 103), 112)

 

 

SSRS Error: Report server url – user does not have required permissions

User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

  1. Run the IE browser as administrator. C:\Program Files\Internet Explorer\iexplorer.exe
  2. Go to Site Settings–>New role assignment–>Type user name and assign system administrator roleSite Settings–>New role assignment–>Type user name and assign system administrator roler01
  3. Go to Home–>Folder settings–>New role assignment–>Type user name and assign to all the rolesr1
  4. Now in normal mode it should work. If not that means you have adjust your UAC settings
  5. Still I was getting the error in normal mode from the IE browser in Report Server. From my local pc I did not get any error message.

 

SSIS: Extract SSRS Reports from ReportServer database

  1. Get the folder list in the SQL Report Server
  2. Using ForEachLoop, Create the folder structure in the local file system
  3. Using ExportColumn, export the the XML file
  4. You can also can use Reporting Services Migration Tools available for SSRS 2008 R2 or after (Sample Reporting Services rs.exe Script to Copy Content between Report Servers)

SQL Query to get the folder list

SELECT DISTINCT Path FROM ReportServer.DBO.Catalog
WHERE Content IS NULL
AND Path NOT LIKE (‘%DATA SOURCES%’)
AND Path NOT IN (”)

SQL Query to extract the SSRS XML file

–The first CTE gets the content as a varbinary(max)
–as well as the other important columns for all reports,
–data sources and shared datasets.
WITH ItemContentBinaries AS
(
SELECT
Path
,CONVERT(varbinary(max),Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
and content is not null
and path not Like (‘%Data Sources%’)
and path not in (”)
),
–The second CTE strips off the BOM if it exists…
ItemContentNoBOM AS
(
SELECT
Path
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varchar(max),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
)
–The outer query gets the content in its varbinary, varchar and xml representations…
SELECT
Replace(Path, ‘/’, ‘\’) AS Path
,CONVERT(varchar(max),Content) AS ReportData –varbinary

FROM ItemContentNoBOM

 

Solution

11/30/2011 – I have created a CodePlex project based on this blog post with some scripts in response to the questions I got in this posts comments. The project is called “SQL Server Reporting Services (SSRS) Catalog Queries”. I have a separate blog post about the project here.

12/17/2011 – I have updated the CodePlex project project and included the SSIS project that extracts catalog contents directly to the file system! w00t!

06/01/2011 – REPOSTED TO FIX FORMATTING ISSUES. LET ME KNOW IF YOU CAN’T READ IT.

Before I even start this entry I should state that the methods I document here, while instructive and useful, are frowned upon by Microsoft, and they are not supported. You should consider getting to the SQL Server Reporting Services (SSRS) item contents via the Web Services provided with SSRS rather than extracting them directly from the database as I describe here. As SSRS is versioned the structure and storage methods used to manage the Report Server items may change, and the methods described here may no longer work. You will have a better chance (although no guarantee) that your code will continue to work against new versions of SSRS if you use the web services instead. Ok, the disclaimer is out of the way.

There have been numerous times that I have wanted to extract the XML of a report (RDL) or the definition of a Shared Data Source (RDS) from the ReportServer database. One of the first times I had this need was as part of a disaster recovery. A client lost the source files for their reports, as well as the reporting services installation. The quickest way to get their report definitions back was to just extract the XML from the ReportServer.dbo.Catalog table’s “Content” column. For a current project I’m working on, I want to determine the database objects (tables, stored procedures, views, functions) that are used by the Datasets in each report. Other times, I have just been curious and wanted a quick way (from within SQL) to view the item definitions.

This blog post, as well as a few to follow document the methods I have developed to do just this.

The ReportServer Database:

SQL Server Reporting Services (SSRS) keeps the various Reports, Data Sources, Images and other resources it provides inside a SQL Server Database. This database may or may not be on the same computer as the Reporting Services instance itself. Also, while the database is named “ReportServer” by default, it could be named something else, so you will need to determine for your installation what instance that database is on, as well as what it’s name is. The scripts below assume the default database name of “ReportServer”.

Inside the ReportServer database there is a Catalog table (ReportServer.dbo.Catalog). The Catalog table stores the items (Folders, Reports, Resources, Linked Reports, Data Sources, Report Models, Report Parts and Shared Datasets) that are available via the SSRS instance. There are numerous columns in the Catalog table, and I won’t describe them all here. However, the ones of interest to this article include:

Column Name Data Type Description
ItemID uniqueidentifier The Primary Key. A system generated GUID.
Path nvarchar(425) The root (“Home”) based path to the item (including its name) in the virtual folder structure provided by the site.
Name nvarchar(425) The name of the report item without the path.
Type int Identifies the type of the item being stored:1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part (SQL 2008 R2, unverified)
8 = Shared Dataset (SQL 2008 R2)
Content image The actual bytes of the report item. If this is a folder or Linked Report, the content is null, otherwise it is stores the binary image of the report item.

Armed with that knowledge you can see that a query to retrieve the binary content for all reports would would like:

01 SELECT
02    ItemID
03   ,Name
04   ,Type
05   ,Content
06 FROM ReportServer.dbo.Catalog

And the result might look like:

ItemID Name                    Type  Content
------ ----------------------- ----- -------------------------
909... AdventureWorks2008R2        5 0xEFBBBF3C3F786D6C2076...
8CC... AdventureWorksDW2008R2      5 0xEFBBBF3C3F786D6C2076...
A20... AdventureWorksLT2008R2      5 0xEFBBBF3C3F786D6C2076...
7BE... Northwind                   5 0xEFBBBF3C3F786D6C2076...
630... Products                    2 0x3C3F786D6C2076657273...
DF6... AnnualSalesByMonth          2 0x3C3F786D6C2076657273...
06F... SalespersonReport           2 0x3C3F786D6C2076657273...
D9E... ParameterTest01             2 0x3C3F786D6C2076657273...

The UTF-8 Byte Order Mark (BOM):

Unicode strings can have a special character called the “Byte Order Mark” at the beginning to indicate the byte order (endianess, big endian, little endian) and encoding (UTF-8, UTF-16, UTF-32) that was used to create the string. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding. it just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one. However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF). That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”. While it is supported, it can cause problems with the conversion to XML, so it may be worthwhile to remove it for Reports (Type=2), Data Sources (Type=5), Report Parts (Type=7), and Shared Datasets (Type=8). You can do that by testing to see if the first three bytes are 0xEFBBBF and then trimming them if they are. Before we do that though, we have to get rid of that pesky image data type used by the Content field.

Casting Out Image:

The ReportServer.dbo.Catalog.Content column uses the deprecated image data type. Unfortunately we can’t perform string manipulations or direct conversions to varchar or xml from the image data type. To fix the problem, we need to first convert (or cast) the image to a varbinary(max), and then perform any string manipulations or additional conversions on the varbinary value.

Getting the Content as Varbinary, Varchar and XML:

Ok, so putting all the stuff from above together, the following code will extract the contents of all Reports, Data Sources, Report Parts and Shared Datasets (Types 2,5,7, & 8) from the database. I’ve broken the query down into a two CTEs and an outer query to isolate the key parts. you should be able to copy the query below, and run it on the same server as your ReportServer database to view the results:

--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
)
--The outer query gets the content in its varbinary, varchar and xml representations...
SELECT
   ItemID,Name,[Type],TypeDescription
  ,Content --varbinary
  ,CONVERT(varchar(max),Content) AS ContentVarchar --varchar
  ,CONVERT(xml,Content) AS ContentXML --xml
FROM ItemContentNoBOM

Here is a screen shot of my results so you can see that the XML type is recognized:

Catalog Content as XML Query Results

Querying the RDL Contents:

Ok, so big deal. We did some trimming and casting and we got XML back. Where this really starts to become useful is when you start using SQL Server’s built in XML data type to parse the XML, and extract key pieces. For example, the following query builds on the query from above and extracts the actual commands used for the each dataset in each Report or Shared Dataset. Cool!

--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
     ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

Here is a screenshot of the results. Again, this would be more impressive on your own machine with your own reports and queries being shown:

Querying the Content XML

I know that the text in the image the above is really small, but you may be able to see that the CommandTextcolumn contains the actual SELECT statements used in each report or shared dataset.

This is just the tip of the ice berg though. Once we have the XML, we can do a lot of different things with it. I hope in the future to post some additional entries that outline creating a set of functions that could be used to parse your report xml in interesting ways. till then, enjoy!

SSRS: Download all RDL files from Report Server in one go.

Original article written by  / 8.7.2013 at 9:01am

Introduction

People working on SSRS are well aware that “Report Manager” does not support downloading all the report files (.rdl files) at one go out-of-box. And I am sure that many of you might have come across this requirement some day or other. Hence, today I will share a simple handy script which would help you to download all the required report files at once.

Implementation

SSRS uses SQL Server to store it’s details as a backend and the Catalog table is used to store the report file in binary form. The below script simply pulls the report definition from the Catalog table & uses BCP utility to export the same at a pre-defined path as a .rdl file.

To use the BCP utility from TSQL, we need to execute  “xp_cmdshell” command; it is disabled by default. So, first you need to execute the below script to enable it –

-- Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- Update the currently configured value for advanced options.
RECONFIGURE
GO
-- Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Update the currently configured value for xp_cmdshell
RECONFIGURE
GO

-- Disallow further advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO
-- Update the currently configured value for advanced options.
RECONFIGURE
GO

Once successfully executed, the below script with the required changes could be executed to download the files –

--Replace NULL with keywords of the ReportManager's Report Path,
--if reports from any specific path are to be downloaded
DECLARE @FilterReportPath AS VARCHAR(500) = NULL
--Replace NULL with the keyword matching the Report File Name,
--if any specific reports are to be downloaded
DECLARE @FilterReportName AS VARCHAR(500) = NULL

--Replace this path with the Server Location where you want the
--reports to be downloaded..
DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\'
--Used to prepare the dynamic query
DECLARE @TSQL AS NVARCHAR(MAX)

--Reset the OutputPath separator.
SET @OutputPath = REPLACE(@OutputPath,'\','/')

--Simple validation of OutputPath; this can be changed as per ones need.
IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = ''
BEGIN
  SELECT 'Invalid Output Path'
END
ELSE
BEGIN
   --Prepare the query for download.
   /*
   Please note the following points -
   1. The BCP command could be modified as per ones need. E.g. Providing UserName/Password, etc.
   2. Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer]
   3. The BCP does not create missing Directories. So, additional logic could be implemented to handle that.
   4. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding.
      It just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one.
      However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF).
      That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”.
      While it is supported, it can cause problems with the conversion to XML, so it is removed.
   */
   SET @TSQL = STUFF((SELECT
                      ';EXEC master..xp_cmdshell ''bcp " ' +
                      ' SELECT ' +
                      ' CONVERT(VARCHAR(MAX), ' +
                      '       CASE ' +
                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+
                      '         ELSE C.Content '+
                      '       END) ' +
                      ' FROM ' +
                      ' [ReportServer].[dbo].[Catalog] CL ' +
                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +
                      ' WHERE ' +
                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x'''
                    FROM
                      [ReportServer].[dbo].[Catalog] CL
                    WHERE
                      CL.[Type] = 2 --Report
                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')
                      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name)
                    FOR XML PATH('')), 1,1,'')

  --SELECT @TSQL

  --Execute the Dynamic Query
  EXEC SP_EXECUTESQL @TSQL
END

Conclusion

Hope, this helps & save a lot of your valuable time.