I’ll be delivering a “Business Intelligence Landscape” session tonight at the UCSD Extension Continuing Education & Career Showcase. In talk I’ll go over what people mean when they say “Business Intelligence”, as well as some of the core concepts and common solutions. We’ll talk about stuff like Extract, Transform and Load (ETL), Data Warehouses, Star Schema, Cubes, Aggregates, MDX, KPIs Reporting Tools, and Data Mining. But if that weren’t enough we’ll see how the cloud and big data are changing the BI landscape as well.

Once you have extracted the catalog item as XML, you can then leverage SQL Server’s built-in support for the XML data type and XQuery to further parse the XML. At this point you need to have an understanding of the Reporting Services Report Definition Language XML Schema and a desire to extract specific pieces of information from a report definition.

Based on the requests I received in my previous blog posts comments, and my own use of these queries in real-world situations, I decided it might be helpful to create a sample SQL Server Management Studio (SSMS) project that contained example objects and queries that could be used to work with the SSRS Catalog contents.

Grab the project and play with the scripts. If you don’t have the SQL Server 2008 R2 client installed, you won’t be able to SSMS project file (I created it using the SQL 2008 R2 version of SSMS), but you should still be able to open the .SQL script files directly in your current version of SSMS.

If you create a script that is useful, let me know and I will review it as a possible addition to the project. Of course, make sure to let me know if you use it, like it, hate it, or have questions or changes!

UPDATE – An updated version of this project has been published to codeplex at: http://ssrscatalogqueries.codeplex.com/. The updated version extracts ALL types of content (not just XML, but resources as well) and also exports the folder structure. It’s really cool!

Jason’s blog post used a combination of Transact-SQL, a For Each loop, and VB.NET Script task to do the work. As I read through his post, I wondered if the SSIS “Export Column” data flow transform help solve the problem and eliminate VB.NET code. Turns out it can, and it makes for a pretty simple package.

Note, here is the source query I will use for my SSRS content. This is based on the queries discussed in my “Extracting SSRS Report RDL …” blog post. I’ll refer to this query as the “Extract Query” in my list of instructions below.

– BEGIN EXTRACT QUERY — 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 determines the appropriate file extension to use –plus it strips off the BOM if it exists… ItemContentNoBOM AS ( SELECT ItemID,Name,[Type],TypeDescription ,CASE Type WHEN 2 THEN ‘.rdl’ –Report Definition Language WHEN 5 THEN ‘.rds’ –Report Data Source WHEN 7 THEN ‘.rsc’ –Report Server Component (? – Guessing) WHEN 8 THEN ‘.rsd’ –Report Server Data (? – Guessing) END AS ExportFileExtension ,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 Name + ExportFileExtension AS ExportFileName ,CONVERT(xml,Content) AS ContentXML FROM ItemContentNoBOM – END EXTRACT QUERY –

Create a new Package in an SSIS BIDS Project. I named my new package “Export SSRS Content.dtsx”

Create a target folder that the SSRS Content will be exported to. I created a folder named “C:\SSRS Content Extracts”

Add a Variable to the SSIS Package to store the path to the directory you just created as a string. I created a variable named “OutputDirectory” and set its default value to “C:\SSRS Content Extracts\” (note the that I included the trailing slash)

Add a “Data Flow” task to your control flow. I named mine “SSRS Content Extract”

Add a Connection Manager that points to the ReportServer database. I named mine “ReportServer DB”

Add an “OLE DB Source” to your data flow

I named mine “SSRS Content Source”

Set it to use the “ReportServer DB” connection manager we just created

Set the “Data Access Mode” to “SQL Command”

I pasted the “Extract Query” shown above as the “SQL command text”

Add a “Derived Column” transform to the dataflow and name it “Generate Export Path”. Drag the green data path from the “SSRS Content Source” to it.

Double click on the “Generate Export Path” transform and add a derived column with the following properties (don’t include the double quotes around the values shown below):

The Data Type and Length will be set for you based on the expression above. (DT_WSTR, 2048)

Add an “Export Column” transform to the dataflow and name it “Export Content”. Drag the green data path from the “Generate Export Path” transform to it.

Double click on the “Export Content” transform and configure the values as follows (again, don’t include the double quotes):

Extract Column: “ContentXML”

File Path Column: “ExportPath”

Allow Append: Cleared

Force Truncate: Checked

Write Byte-Order Mark: Cleared

There you have it. Pretty simple. A couple of things to note.

The expression I used in step 8.3 assumes that the “OutputDirectory” variable value includes the trailing forward slash.

The “Force Truncate” checkbox set in step 10.4 causes existing files to be overwritten

The resulting data flow looks like this:

You can now run this package (and even supply an alternative export path via the OutputDirectory variable) to export all reports, data sources, report parts, and shared datas sets from an SSRS 2008 R2 installation.

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.

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:

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:

I know that the text in the image the above is really small, but you may be able to see that the CommandText column 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!

It’s SQL Saturday time in the OC again, and this time around I am doing a presentation on SQL Server 2008 R2 Map Visualizations. I am notoriously bad about forgetting to post slides, and demos, so this time I am actually posting them!!

This zip file (17.8MB) includes the slide deck, my SpatialData sample database, and the BIDS projects. You will need to restore the backup of the SpatialData database on a SQL 2008 R2 instance, and then likely edit the connection string in the shared data source.

Ok, so I’m a nerd. I’m also a Microsoft Certified Trainer, and I teach a lot of SQL courses so as a result I work with AdventureWorks databases a fair amount. When I am building reports in SSRS that use AdventureWorks I like to have a nice looking logo to use, and the ones that are provided with the curriculum or are available online leave much to be desired.

I took it upon myself to create a higher resolution version of the logo files, and thought I would post them here for other control-freak-nerds like myself to use. As long as Microsoft doesn’t give me grief about copying and re-distributing their logo, then I’ll leave this up here.

Use the links below to download a .zip file with illustrator, corel, photoshop, xaml, jpg, png, and other formats of the logo. Enjoy.

I just posted this information as an update to an earlier post about problems with the Reporting Services /ReportServer and /Reports virtual directories sometimes not responding on port 80. Today I realized that my problem wasn’t limited to just Reporting Services. This morning, none of my IIS hosted sites were responding. I’d get 404’s for anything I tried.

It turns out that by default, Skype will use both ports 80 and 443 as alternatives to its regular port for incoming connections (for firewall purposes). If Skype get’s to port 80 before the System does (not sure how, but it happens), then Skype grabs the ports and prevents the other services for receiving any requests.

To turn this off in Skype, open Skype and go to “Tools” | “Options”, on the left hand side of the options dialog select “Advanced” | “Connection” and then turn off the checkbox for the “Use port 80 and 443 as alternatives for incoming connections”.

A Quick FYI, the tools I used to figure it out were Fiddler, the netstat command line utility included with windows, and the Windows Task Manager, and the web.

First, Fiddler. As I used fiddler to try and browse local sites, I got a VERY basic 404 Response back. No server information or anything. That let me know that I wasn’t hitting IIS.

Next, netstat. To figure out which process was grabbing my port 80 traffice I used “netstat –ao” and saw the Process ID 2384 was the process listening on port 80.

On to Task Manager. I looked that up in my task manager, and found that it was Skype.

Finally a web search. Did a quick search on Skype and Port 80 and found a number of hits discussing the same problem.

Normally, the System process should be grabbing port 80. If you open a command prompt using the “Run as Administrator” option and run “netstat –ao” you should see the PID (Process ID) that is listening on each port. Then open up your Task Manager (or better yet Process Explorer). In task manager, switch to the “Processes” tab and use the “View” | “Select Columns…” menu to enable the “PID (Process ID”) column to be displayed. You should see something similar to the following:

Today however, when I looked I found Skype’s process ID, not System’s. (Sorry, I didn’t take a screen shot when I had it).

This isn’t a problem if you are using Visual Studio .Net’s development web server (aka Cassini) because it uses a dynamically chosen port for each site. It could be a problem if you are hosting any sites in IIS or trying to use things like SQL Server Reporting Services which has web based components that want to listen on port 80.

So I put this here for me to remember the problem, and in hopes that it may help some other frustrated developer out there.

Here is the slide deck from the Introduction to SQL Server Reporting Services sessions I gave at SQL Saturday 44 in Huntington Beach, CA earlier today! Thanks to everybody who came, and sorry for the technical problems getting the Report Manager to load. It’s back to the same problem I was having about port 80 not getting registered right on Windows 7. I thought I had that fixed, but I guess not.

Anyhow grab the slides, and drop me a comment or an email if you have a question!

UPDATE! (07/27/2010) – Even with the original post claiming to have fixed my problem, I have had repeated reoccurrences of situations where all of a sudden, it just didn’t work. This seemed random and unpredictable. In fact this morning, I realized my problem was limitied to just the reporting services /ReportServer and /Report virtual directories. It was actually with any IIS hosted web. I normally do web dev in a VM, so I haven’t noticed that problem until today. But in the middle of prepping some demos, websites that worked locally yesterday all of a sudden didn’t work this morning. 404. File not Found!

I now believe I know the problem……..ITS SKYPE!!!!! Skype? Yes! Skype! There is an option in Skype to allow it to use port’s 80 and 443 as an alternative to its regular port. This option appears to be on by default (I certainly never turned it on myself) and for a normal end user is probably a great choice. Turns out in my case though (and with other developers or anybody that needs to host their own local webs on port 80) this could cause problems. Not sure why it was never a problem on my previous OS’s. I haven’t really seen this as an issue until Windows 7. Maybe its a new option in Skype. Not sure. Anyhow, it appears that once I have this option turned off, I am once again able to access my reporting services web sites (any any other IIS website for that matter) on port 80 again.

The randomness seems to be that sometimes Skype would get to the ports first and grab them. The other port 80 based services then wouldn’t get their messages. On other occasions, Skype would get there last, and the other port 80 services would work.

To turn this off in skype, open Skype and go to “Tools” | “Options”, on the left hand side of the options dialog select “Advanced” | “Connection” and then turn off the checkbox for the “Use port 80 and 443 as alternatives for incoming connections”.

Original post (04/08/2010):

I finally got around to refreshing my Windows 7 installation about a month ago. I had been running SSRS 2008 on Windows 7 RC without issue, but after re-installing everything on the release I had a problem with the “/Reports” and “/ReportServer” URL reservations. I would get a 404 not found from IIS when I tried to access either of them.

I poked around a little bit and the best suggestion I found was to use the “Reporting Services Configuration Manager” to change the ports for the URL reservations to something other than port 80 (Like port 8080), and for a quick fix that worked. I just didn’t understand why I had to.

This morning, I was determined to figure it out. Unfortunately I didn’t document my steps better because now it works, and I can’t actually make it NOT work again. Here are the two things I did that MIGHT have made the difference:

I ran the “Reporting Services Configuration Manager” as administrator by right-clicking on it and choosing “Run as Administrator”

I added host header entries in the “Advanced” buttons for “localhost” on port 80 for both the Web Service URL and Report Manager URLs.

I removed the host header entries, retested, and everything still worked.

It feels like there was just some block in Win7 that we keeping the original port 80 reservations from working, and I somehow cleared the blockage with one of the above. I had changed the URL reservations in the past (from port 8080 and back to port 80, etc) but nothing did the trick. Something I did today caused it to finally start working.

I’ll be speaking at the Orange County .Net user group (www.ocdotnet.org) this evening (02/09/2010) on SSRS 2008 for .NET Developers. I’ll cover the range of options that developers have for enhancing, extending and integrating SQL Server Reporting Services.

You can get the slide deck, and the sample project for my session here: