Business Intelligence Landscape Presentation

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.

Feel free to grab a copy of my slides:

Business intelligence landcape

Enjoy.

Extracting SSRS Content using the SSIS “Export Column” Component

Download the sample project for this blog post (29kb)

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!

A couple of months back I posted an article on “Extracting SSRS Report RDL (XML) from the ReportServer Database”.  Recently, Jason Brimhall posted an article that used similar logic to do a “SSRS Export En Masse”.  It’s a great example of how queries against the ReportServer.dbo.Catalog table can be used to recover reports that have been published to the report server, even if you have lost the source files. 

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  –


Here are the steps I followed.  You can download my SSIS project here.

  1. Create a new Package in an SSIS BIDS Project. I named my new package “Export SSRS Content.dtsx”
  2. Create a target folder that the SSRS Content will be exported to.  I created a folder named “C:\SSRS Content Extracts”
  3. 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)
  4. Add a “Data Flow” task to your control flow.  I named mine “SSRS Content Extract”
  5. Add a Connection Manager that points to the ReportServer database.  I named mine “ReportServer DB”
  6. Add an “OLE DB Source” to your data flow
    1. I named mine “SSRS Content Source”
    2. Set it to use the “ReportServer DB” connection manager we just created
    3. Set the “Data Access Mode” to “SQL Command”
    4. I pasted the “Extract Query” shown above as the “SQL command text”
  7. 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.
  8. 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):
    1. Derived Column Name: “ExportPath”
    2. Derived Column: “<add as a new column>”
    3. Expression: “(DT_WSTR,2048)(@[User::OutputDirectory] +  [ExportFileName])”
    4. The Data Type and Length will be set for you based on the expression above. (DT_WSTR, 2048)
  9. 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.
  10. Double click on the “Export Content” transform and configure the values as follows (again, don’t include the double quotes):
    1. Extract Column:  “ContentXML”
    2. File Path Column: “ExportPath”
    3. Allow Append: Cleared
    4. Force Truncate: Checked
    5. 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:

SSRS Content Extract Data Flow

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. 

Download the BIDS project here.

Speaking at San Diego SQL Users Group Tonight!

I will be giving two talks at the San Diego SQL Server Users Group meeting tonight. I will be doing my presentations on "Understanding SSIS Control Flows" and "Understanding SSIS Data Flows".  If you are new to SQL Server Integration Services (SSIS), or even if you have been using it for some time, I think you will find them interesting and you will probably learn something as well. 

UCSD Sorrento Mesa Extension
6925 Lusk Blvd (AT&T Building)
San Diego, CA 92121
Click here for a map

Time: 6:00pm

Hope to see you there!

LA Code Camp Slides…

Ok, here they are.  If you attended my sessions you know they are more demo than slides so it is questionable how much value you can get from the decks themselves, but people asked and I promised so here they are.  I would like to do screen captures of these and hope to do that soon.  If and when I get that done I will post links on the blog.

Sessions:

Enjoy!