1. Problem
The problem that our business is facing now is that we lack documentation of our datasets for the reports. Since we’re working on a Project to build a big data dictionary in Confluence we would need all the SQL used in our reports exported and documented. We’ve got a lot of reports on our Reporting Server and exporting each Dataset manually will be extremely time-consuming. The SSIS Project that I’ve built automates this problem and speeds up the documentation process significantly.
2. Solution
2.1. SQL Query
After doing a bit of reading around how I can return the dataset sql from the reporting Server I came across an article where somebody had written this query. You can find a copy of the main query in Appendix A.
Results
After running the query against our Reporting Server I realised how useful it was and drew up a design of an SSIS task that will help us speed up our documentation process.
2.2. SSIS Project Details
2.2.1. Overview
This project is meant to extract the SQL from all the reports in our reporting server and organise them in the same folder structure as on the Reporting Server.
The job has these steps:
1. Archive tbl_SSRS_SQL to tbl_SSRS_SQL_Archive
2. Extract All Reports version 2010
3. Extract All Reports version 2008
4. Get Table and File Names and store them into variables
5. For Each Table and File Name
a. Create Folder Level
b. Truncate SQL File
c. Export SQL
d. Archive File
Connection Managers Used:
Job Structure:
2.2.2. Package Details
2.2.2.1. Create and Populate tbl_SSRS_SQL and tbl_SSRS_SQL_Archive
This step is covered by both dataflow tasks All Reports in 2010 and Store Result in 2008.
Start by creating the 2 tables – tbl_SSRS_SQL and tbl_SSRS_SQL_Archive.
CREATE TABLE "tbl_SSRS_SQL" (
"ReportName" nvarchar(425),
"ReportPathLocation" nvarchar(425),
"Path" nvarchar(425),
"DataSetName" nvarchar(678),
"DataSoureName" nvarchar(250),
"CommandText" nvarchar(2500),
)
CREATE TABLE "tbl_SSRS_SQL_Archive" (
"ReportName" nvarchar(425),
"ReportPathLocation" nvarchar(425),
"Path" nvarchar(425),
"DataSetName" nvarchar(678),
"DataSoureName" nvarchar(250),
"CommandText" nvarchar(2500),
ArchivedDate datetime default CURRENT_TIMESTAMP
)NB! Notice That the archive table has ArchivedDate column as get_date. This would record the date a change has been made in our datasets. Structure of Data Flow Task
1. Bs-SQL Query from Report Server
2. Lookup – Record Match Or Not
In this instance, I’m using DataSetName as Primary key.
Select the column and map it in the Lookup Transformation.
3. BS-SQL OBKFeeds tbl_SSRS_SQL Destination
4. Record Match OutPut – (Lookup Transformation) – Record Updated or not
If the record matches.
5. OLE DB Command – Update table with Changed Data
NB! Note that in our update statement we put the last parameter as DataSetName (since it’s our primary key for the table) that is why we map it to param_5.
The 3 data Flow Tasks have the same design and do the same things. There are small changes in the SQL Sources but the logic is the same.
2.2.2.2. Export SQL Files and Create Report Server folder Structure
1. Set Up Folder and File Structure
The folder you need is a 01Template folder where you would keep your empty sql file which would replace the one that you’ll populate with SQL text which is outside 01Template folder.
2. Next step is to store Folder Paths and File Names into Variables.
Firstly, create the variables you would need.
Create the variables and make sure your data types match. Don’t worry about the expressions at the minute.
The execute SQL task will pass a resultset into @ResultSet which is a system object parameter. The @ResultSet would then be used in our ForEach Loop and Map the parameters for @TableName (which is actually our folder path) and @FileName. The last 2 parameters have expressions in them which would be discussed later.
SQL Statement:
SELECT ISNULL(MD1.Path, '') + '/' + ISNULL(DataSoureName, '') + '/' AS Folders
, DataSetName
FROM
(
SELECT CAST(ReportName as varchar(max)) ReportName
, CAST(ReportPathLocation as varchar(max)) ReportPathLocation
, CAST(Path as varchar(max)) Path
, CAST(DataSetName as varchar(max)) as DataSetName
, CAST(DataSoureName as varchar(max)) DataSoureName
, CAST(CommandText as varchar(max)) CommandText
FROM tbl_SSRS_SQL
) AS MD1
WHERE(1 = 1)
ORDER BY Folders;
The ResultSet of the table is giving us the 2 parameters which would map to these columns. DataSetName would map to @FileName, Folders would map to @TableName.
Make sure that Result Name is 0!
Parameters with expressions:
· @DestinationFolders
· @FinalFileName
With the Parameters passed appropriately folders and subfolders would be created and files will be allocated to the correct place.
3. For Each table and File Name
ForEach Loop Configuration
You pass the object here which would map variables appropriately.
Make sure in your variable mappings you start from 0.
The first task is to Create the Folder Level which is done by a file System Task.
Secondly we Truncate the SQL file which is done by a file system task which copies the empty file from the ..\ssrs\01Template folder and replaces it with the one in the ..\ssrs\ folder.
Next step is Export the SQL and put it in a file.
This is done by a data flow task which gets the data from tbl_SSRS_SQL and Puts it into a SQL File.
Last Step is Archiving the files to the correct folders with the use of @FinalFileName Parameter.
3. Project Summary
The result of the SSIS Project are:
1. Different Folder Levels
2. SQL Files Allocated to the correct Place
3. Table Stored and in our database that we can quickly query without any advanced SQL.
4. Archive table in our database that keeps track of our changes of SQL
Appendix A
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition)
SELECT MD1.*
FROM
(
SELECT CATDATA.Name AS ReportName
, CATDATA.Path AS ReportPathLocation
, STUFF(CATDATA.Path, 1, 1, '') AS Path
, CATDATA.Name + ' - ' + xmlSorceumn.value('(@Name)[1]', 'VARCHAR(250)') AS DataSetName
, xmlSorceumn.value('(Query/DataSourceName)[1]', 'VARCHAR(250)') AS DataSoureName
, xmlSorceumn.value('(Query/CommandText)[1]', 'VARCHAR(2500)') AS CommandText
FROM
(
SELECT C.Name
, c.Path
, CONVERT(XML, CONVERT(VARBINARY(MAX), C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content IS NOT NULL
AND C.Type = 2
) CATDATA
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable(xmlSorceumn)
) AS MD1
WHERE(1 = 1)
ORDER BY md1.ReportName;