Framework for Automating SQL Server Reporting Services (SSRS) data driven subscriptions – Part 1
Description: What the goal was to find a way to automate how an end user could subscribe to a SSRS Data Driven Subscription without any intervention from the Business Intelligence Developer. And also enable the end users to have their own selections. And finally if they did not want to receive the report any longer to then delete their subscription.
In Part 1 we will be creating the SharePoint List, and using SQL Server Integration Services to get the data into our table.
· What we will be doing below is to first create a SharePoint List
o NOTE: This is created on SharePoint 2013
§ And for SSIS we will be using SQL Server 2012
· Then we will use SSIS and a new Data Flow Task to get the data out of our SharePoint List and put this into a SQL server table.
· After which we will then transform some of the data and then store it in a Dimension table.
o This dimension table will then be used later in a SSRS Data Driven subscription so that we can email the report out.
Example for documentation:
· We are going to create our List on SharePoint 2013
o Within our List we are going to have the following fields:
§ Subscription Name
§ Date Range
· We will specify custom Date Ranges
§ Product Line
· We will pre-populate the Product Line list
§ Email Address
· All the above information will be coming from the AdventureWorks2012DW SQL Database
You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330
· I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012
Creating SharePoint 2013 List
1. In your SharePoint 2013 website click on the Site Contents:
a. 
2. Then click on add an app
a. 
3. Then click on Custom List, this is so that we can create our own Custom List
a. 
4. This will bring up the Adding Custom List Window
a. As with our example we created the following Custom List Name:
i. 
b. ClickCreate
5. Now click on your Custom List you created to go into your Custom List
a. 
6. Now at the ribbon on the top click on List and then List Settings on the right hand side
a. 
b. This will then take you to the List Settings
7. As per our Example explained above we are going to be creating the following columns detailed below by doing the following.
8. Under Columns click on Create column:
a. 
b. First we created our Subscription Name by clicking on Title and changing it to the following:
i. 
ii. Click Ok to create the column
c. Click on Create Column again to create the Date Range.
i. NOTE: We are going to create names for our Date Range which we will convert to actual dates when we insert the data in SSIS in the later steps.
1. The choices we are going to create are the following due our data having actual data from July 2007 to June 2008
2. Ranges:
a. July 2007
b. July 2007 – December 2007
c. July 2007 – June 2008
3. 
4. Click Ok to create
d. Click Create column again for the Product Line.
i. Once again it will be a choice of the following:
1. Accessory
2. Mountain
3. Road
4. Touring
ii. 
iii. Click Ok to Create
e. The final column to be created is the Email Address column as shown below:
i. 
ii. Click Ok to Create.
9. So once completed you will see the following:
a. 
10. Next we will use SSIS to bring the data down into a SQL Table.
Downloading and installing the SSIS component to query data from SharePoint Lists
1. Click on this link below and download the following:
a. http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652
2. 
3. Once it has been downloaded, you can run the MSI and complete the steps.
a. NOTE: You will have to close down and open up your BIDS (Business Intelligence Development Studio) or SSDT (SQL Server Data Tools) in order to see the SharePoint List data.
Creating the SSIS Package to get the data from the SharePoint list into a SQL Server Table.
1. Create a new SSIS Package and give it an appropriate name.
a. As with our example we named it the following:
b. 
2. The next thing that you need to do is to create a connection to your SharePoint Server by doing the following:
a. Right click on the Connection Managers and select New Connection.
b. Scroll down and select the following:
i. 
c. Then click Add which will bring up the SharePoint Credential Connection Manager Editor.
d. As with our example we put in the following:
i. 
ii. NOTE: Make sure that the SQL Server Agent Job service account has the permissions to SharePoint.
1. Alternatively use a Custom Credential which will require access to the SharePoint List.
2. In our environment we have got a domain account who’s password does not change that we use.
3. Next drag in a Data Flow Task and as with our example we gave it the following name:
a. 
4. Now go into your Data Flow Task and configure it with the following:
a. Click in the SSIS Toolbox and under Common you will see your SharePoint List Source
i. 
b. Drag this into your Data Flow Task and configure it with the following:
i. Double click to go into the Properties
ii. On the connection Managers Window you have to select your connection Manager:
1. 
iii. Then click on the Component Properties and put in the following:
1. 
a. This is so that it will look for all the SharePoint Lists
2. 
a. This will be the actual Root of your SharePoint location
b. NOTE: It must NOT be the actual URL To your list, if you do this it will not find the List.
3. Then scroll up to the Site List Name and in here you will put your List that you created above.
a. As shown below is our example:
b. 
iv. Now click on Column Mappings and if the above settings are correct you should see all the columns as shown below:
v. 
c. Then click Ok to complete your SharePoint List Source
5. Next is to create the destination and for this you need to create your destination table first.
6. Below is the scripted out table that we used:
CREATETABLE[dbo].[Staging_tb_SharePointList](
[ID][int]NOTNULL,
[Date Range][nvarchar](255)NOTNULL,
[Product Line][nvarchar](255)NOTNULL,
[Email Address][nvarchar](255)NOTNULL,
[Subscription Name (LinkTitle)][nvarchar](255)NOTNULL,
[Subscription Name (Title)][nvarchar](255)NOTNULL,
[Subscription Name (LinkTitleNoMenu)][nvarchar](255)NOTNULL,
[ContentType][nvarchar](255)NOTNULL,
[Modified][datetime]NOTNULL,
[Created][datetime]NOTNULL,
[CreatedBy][nvarchar](255)NOTNULL,
[ModifiedBy][nvarchar](255)NOTNULL,
[Version][nvarchar](255)NOTNULL,
[Attachments][int]NOTNULL,
[Edit][nvarchar](255)NOTNULL,
[Type][nvarchar](255)NOTNULL,
[ItemChildCount][nvarchar](255)NOTNULL,
[FolderChildCount][nvarchar](255)NOTNULL,
[AppCreatedBy][nvarchar](255)NOTNULL,
[App Modified By][nvarchar](255)NOTNULL,
CONSTRAINT[PK_Staging_tb_SharePointList]PRIMARYKEYCLUSTERED
(
[Created]ASC
)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]
)ON[PRIMARY]
GO
a. NOTE: Some of the columns from the source might be set to nvarchar(4000) but I know that we would not have anything this long so we left them all at 255 to save space.
7. Next we dragged in our OLE DB Destination:
a. NOTE: You would have to have created a Connection to your Destination Database
b. 
c. As you can see we configured it with the following:
i. 
d. And mapped the columns as required.
i. NOTE: Ensure that all the mappings are correct
8. Once done it should look like the following:
a. 
b. As noted above the reason for the warning is due to some columns from the source having nvarchar(4000)
9. Now the final step is to run it and see if it works as expected
a. But before doing this first add an item to the list:
b. 
c. ClickSave
10. Now run your data flow task and you should hopefully see 1 row transferred
a. 
Creating the Stored Procedure to insert the data into the Dimension (storage table) – To be used in Data Driven Subscription
Below is the Stored Procedure (SP) so that we can store the details from the SharePoint List and create some additional column.
The destination table will also be used later in the Reporting Services data driven subscription.
1. Below is the SP which you can use to create your SP.
a. NOTE: You will have to first create the destination table, which you can complete within the SP.
USE[AdventureWorksDW2012]
GO
/****** Object: StoredProcedure [dbo].[prc_LoadDIM_Mart_TD_SharePoint_ReportSubscription] Script Date: 2014-04-23 07:50:25 AM ******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
— ===============================================================================================================
— Disclaimer
— The sample scripts are provided AS IS without warranty of any kind.
— I further disclaims all implied warranties including, without limitation, any implied warranties of
— merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance
— of the sample scripts and documentation remains with you.
— In no event shall I, its authors, or anyone else involved in the creation, production, or delivery of
— the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business
— profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use
— of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the
— possibility of such damages.
— ===============================================================================================================
— ===============================================================================================================
— Author: Gilbertq
— Create date: 16 Apr 2014
— Description: Here we are going to Define our Start and End Dates for our selection
— from our SharePoint List
— NOTE: The format of the Start and End Dates are going to be the DateKey
— from our Date Table so that this can be passed to our SSAS
— AdventureWorksDW2012 cube.
— ===============================================================================================================
ALTERPROCEDURE[dbo].[prc_LoadDIM_Mart_TD_SharePoint_ReportSubscription]
AS
BEGIN
SETNOCOUNTON;
— ===========================================================================
— 1. Getting the DateKey for our Selection: 01 July 2007
— ===========================================================================
Declare@Selection_StartJuly2007asInt
Set@Selection_StartJuly2007= 20070701
— ===========================================================================
— 2. Getting the DateKey for our Selection: 31 July 2007
— ===========================================================================
Declare@Selection_EndJuly2007asInt
Set@Selection_EndJuly2007= 20070731
— ===========================================================================
–3. Getting the DateKey for our Selection: December 2007
— ===========================================================================
Declare@Selection_December2007asInt
Set@Selection_December2007= 20071231
— ===========================================================================
–4. Getting the DateKey for our Selection: June 2008
— ===========================================================================
Declare@Selection_June2008asInt
Set@Selection_June2008= 20080630
— ===========================================================================
— Below is the query to either update an existing Subscription or if new
— then insert into the table
— ===========================================================================
Merge[dbo].Mart_TD_SharePoint_ReportSubscriptionsas[Dest]
Using (
SELECT [ID]
,[Date Range]
,[Product Line]
,Casewhen[Product Line]=‘Accessory’then‘S’
when[Product Line]=‘Mountain’then‘M’
when[Product Line]=‘Road’then‘R’
when[Product Line]=‘Touring’then‘T’
Endas[Product Line For Query]
,[Email Address]
,[Subscription Name (Title)]
,[Modified]
,[Created]
,[CreatedBy]
,[ModifiedBy]
,Casewhen[Date Range]=‘July 2007’Then@Selection_StartJuly2007
when[Date Range]=‘July 2007 – December 2007’Then@Selection_StartJuly2007
when[Date Range]=‘July 2007 – June 2008’Then@Selection_StartJuly2007
EndasStartDateKey
,Casewhen[Date Range]=‘July 2007’Then@Selection_EndJuly2007
when[Date Range]=‘July 2007 – December 2007’Then@Selection_December2007
when[Date Range]=‘July 2007 – June 2008’Then@Selection_June2008
EndasEndDateKey
FROM[AdventureWorksDW2012].[dbo].[Staging_tb_SharePointList]with (nolock)
)as[Source]
On[Dest].[ID]=[Source].[ID]
and[Dest].[Email Address]=[Source].[Email Address]
and[Dest].[Subscription Name (Title)]=[Source].[Subscription Name (Title)]
WhenMatched
ThenUpdateSet
[Dest].[ID]=Source.[ID]
,[Dest].[Date Range]=Source.[Date Range]
,[Dest].[Email Address]=Source.[Email Address]
,[Dest].[Subscription Name (Title)]=Source.[Subscription Name (Title)]
,[Dest].[Modified]=Source.[Modified]
,[Dest].[Created]=Source.[Created]
,[Dest].[CreatedBy]=Source.[CreatedBy]
,[Dest].[ModifiedBy]=Source.[ModifiedBy]
,[Dest].StartDateKey=Source.StartDateKey
,[Dest].EndDateKey=Source.EndDateKey
,[Dest].[Product Line]=Source.[Product Line]
,[Dest].[Product Line For Query]=Source.[Product Line For Query]
WhenNotMatchedbyTargetthenInsert (
[ID]
,[Date Range]
,[Email Address]
,[Subscription Name (Title)]
,[Modified]
,[Created]
,[CreatedBy]
,[ModifiedBy]
,StartDateKey
,EndDateKey
,[Product Line]
,[Product Line For Query]
)Values (
Source.[ID]
,Source.[Date Range]
,Source.[Email Address]
,Source.[Subscription Name (Title)]
,Source.[Modified]
,Source.[Created]
,Source.[CreatedBy]
,Source.[ModifiedBy]
,Source.StartDateKey
,Source.EndDateKey
,Source.[Product Line]
,Source.[Product Line For Query]
)
WhenNotMatchedBySource
ThenDelete
;
SETNOCOUNTOFF;
END
b.
c. With the above SP the reason for creating the StartDateKey and EndDateKey variables is due to our choice in the SharePoint list:
i. 
ii. And the above correlates to our variables in the SP.
iii. NOTE: The StartDateKey and EndDateKey will be used later in the SQL Server Reporting Services (SSRS) Data Driven subscription, which in turn will pass the information to the SSRS Report Parameters
d. We also had to put in a Case statement for the Product Line For Query so that it would match the underlying values in our SSAS dimension
e. Another thing that we did do is to validate the email address to ensure that there are no bogus email addresses.
2. So this is what the table will look like with the query for the Data Driven Subscription
3. 
The next steps will be in the Reporting Services quick wins document where we will explain how we create our report with the require parameters.
After which we then create our Data Driven subscription
And then finally create our Power View Report and pass this link with the filter in our SSRS report.
[…] Here is the link to Part 1: FRAMEWORK FOR AUTOMATING SQL SERVER REPORTING SERVICES (SSRS) DATA DRIVEN SUBSCRIPTIONS – PART 1 […]
[…] This follows on from the FRAMEWORK FOR AUTOMATING SQL SERVER REPORTING SERVICES (SSRS) DATA DRIVEN SUBSCRIPTIONS – PART 1 and FRAMEWORK FOR AUTOMATING SQL SERVER REPORTING SERVICES (SSRS) DATA DRIVEN SUBSCRIPTIONS – […]