How To Automate SharePoint Report Creation With SSIS and SSRS

If you’ve used SQL Server Reporting Services to any great extent, you’ve likely encountered the need to generate reports automatically. This requirement may be for for delivery purposes, archival purposes, or simply to reduce report rendering wait times for the end users. SSRS supports this requirement out of the box – a report administrator can set up a subscription, enter the required parameters, and the report will be generated and delivered on that schedule.

This approach is highly declarative, and puts the onus of subscription creation on the report administrator. To this end SSRS also supports data driven subscriptions, which allow the subscriptions to be looked up from a SQL table. How that table is maintained is up to the individual organization, but it does allow a measure of dynamism. With SQL Server Reporting Services 2012, this feature is made much more user friendly through the use of User Driven Subscriptions.

The down side to any of this dynamic behaviour is that in every case, it requires the Enterprise version of SQL Server (with SQL Server 2012, the BI SKU also has this capability). In addition, with SQL Server versions prior to 2012, the capability is somewhat less than user friendly.

In this post, I will outline a methodology that will allow you to provide SharePoint list based report subscriptions that will allow users to subscribe to published reports, and have them published to a SharePoint document library. The approach is not restricted to SharePoint – indeed it could be used with native mode to read through a list of possible parameter values, and email the resulting reports, or store them in a file system, but the SharePoint example is the one that I will be using below.

I should also point out that although the examples below use SQL Server 2012, the approach should work with versions back to SQL Server 2005.

The primary components of this solution are a SharePoint list that will be read to determine what reports to render (the subscription list), a SQL Server Integration Services (SSIS) package that will read through the subscription list and use the values therein to render the report, and finally, a SharePoint document library that will house the reports. Of course, we also need a report to be rendered, and in our case, this report is also stored in a SharePoint document library, as Reporting Services is running in SharePoint Integrated mode.

The good news, is that all of the constituent portions of this solution are either downloadable for free, or come with SQL server in any other edition besides Express. Therefore, the chances are that if you have SharePoint, then you already have all of the tools that you need.

Step 1 – Obtain the SharePoint List Source and Destination Project

Out of the box, SSIS doesn’t know how to talk to SharePoint data. Fortunately, there’s an excellent Codeplex project that adds the required capability. If you haven’t already done so, download the SharePoint List Source and Destination project from Codeplex. You will find a good blog post on working with this tool here. Once installed, you will be ready to build the solution. Of course, this step is only necessary if you want to use a SharePoint list as a subscription source.

Step 2 – Create your subscription and report library

In this solution, we will allow a user to enter a subscription request in our subscription list. The user can specify the URL of the report to be run, the parameters for the report, the file type that is to be produced, and the library where the report is to be stored. In order to support this, we’ll need at least one document library where the produced reports will be stored, and one custom list.

Create your document library, and note its URL. In our case below, our report library will be at http://home.nautilusinc.local/sites/nmarine/IT/Sandbox/ExpenseReportOutput. This URL will be used below. In this library, we don’t need to add any custom metadata properties, but you certainly may, should you wish to do so.

Next, create a custom list. In our case, the list will be named “Subscriptions” and will be created in the  “http://home.nautilusinc.local/sites/nmarine/IT/Sandbox” site. Where you create this list is not important, but what is important is the display name of the list, and the URL of its parent site.

For our use case, we want the user to be able to specify the Report to be rendered, the destination to place the rendered reports, the parameters to use for the rendered report, and the file type of the rendered report. To that end, we will add 4 additional columns to the list, as shown below.

image

You will also note that the “Title:” field has been renamed to “Subscription” on this list. This is purely for cosmetic purposes. Three of the new fields are simply single line text fields, while the Format field is choice. In our example, the options available for the choice field are WORDOPENXML, PDF, EXCELOPENXML, IMAGE, and NULL. You can allow any of the possible output types that Reporting services supports. I have outlined these types previously in another post here.

While it is outside the scope of this article, you will likely want to modify the form to display more user friendly names for the options than “WORDOPENXML”, etc, and automatically calculate the value for the subscription field. InfoPath would be an excellent tool to do this with, and there are other alternatives as well. For our purposes, we will work with the form as is.

Once done, you will want to add a couple of subscriptions. In our case, we’re working on a very simple report as shown below:
image

The report takes a single parameter, employee name, and renders the report filtered by that parameter. The subscription list item that we’ll create will look something like below:

image

The value for ReportURL is the full URL path to the report definition, in our case it is http://home.nautilusinc.local/sites/nmarine/finance/ReportsMarch22/ExpenseReports.rdl (you should be able to enter the URL into a browser and see the report), and the destination library is the full URL path to the destination library, in our case http://home.nautilusinc.local/sites/nmarine/IT/Sandbox/ExpenseReportOutput.

After adding two subscriptions, our subscription list appears as follows:

image

When our job runs (defined below) it will iterate through this list and create a corresponding PDF file and Word file in the destination library. Next, we create the SSIS package that will actually do the work.

Step 3 – Create a Reporting Services Web Service Proxy Class

In order to render the Reporting Services reports, we will need to call the Reporting Services web service from a SSIS Script task. In order to do that, we’ll need to use a proxy class. Luckily, we can just generate one using the WSDL.EXE generation tool available from the .Net 3.5 SDK. You run the tool with the following options:

wsdl.exe /language:[language choice] /out:ReportService.[language choice] http://[SPSiteURL]/_vti_bin/ReportServer/ReportService.asmx?WSDL

where:

  • [language choice] = VB or CS
  • [SPSiteURL] = URL of the SharePoint Site Collection

In our case, the precise command is:

wsdl.exe /language:VB /out:ReportService.vb http://home.nautilusinc.local/sites/nmarine/_vti_bin/ReportServer/ReportExecution2005.asmx?WSDL

If you don’t want to build your own, you can download the one that I created for this project (it’s Visual Basic). It was built using SSRS 2012, but should be backward compatible. Also, don’t forget to change the embedded server URLs.

Once you have the output file, make note of its location – we’ll use it below when creating the script task in SSIS.

Step 4 – Build the SSIS Package

I’m going to assume that most people reading this have little or no exposure to SSIS, so I’ll try to be as detailed as possible. You’ll need to start SQL Server Data Tools (if you’re using SQL Server 2012) or Business Intelligence Development Studio (for SQL versions prior to 2012).

image

You may notice that is has a striking resemblance to Visual Studio 2010. That’s because it is VS2010.  Select “New Project” then in the “Business Intelligence” section, select “integration Services Project”. Give the new project a name and location and click OK.

image

Once created, we’ll need to create a SharePoint List connection manager. From the Solution explorer, right click on “Connection Managers” and select “New Connection Manager”. Scroll down on the window, select “SPCRED” and click Add.

image

You will only see SPCRED if you completed Step 1 above. The Connection Manager will then prompt for a name and a set of credentials. Provide the name, and also provide it with an account that has access to the subscription list. If the SSIS service account has access, you can select “Use Credentials of Executing Process”, otherwise provide a service account with access.

image

We’ll be working within a Data flow task, so drag a Data Flow Task onto the design canvas.

image

Next, double click on the data flow task, or click on the Data Flow tab to bring up the Data Flow Task Editor. From there, drag a “SharePoint List Source” action onto the canvas. (Note: if the SharePoint List Source does not appear, there may have been a problem installing it. Consult the documentation for the SharePoint List Source and Destination project for troubleshooting steps.) Double Click on the List Source action to configure it. The first item to configure is the Connection Manager. Simply select it from the (hidden!!! ) drop down list. Click on the area beside “SharePoint Credential Conn…” to reveal the dropdown.

image

Next, click on the “ Component Properties” tab. Here, you perform the bulk of the action configuration. There are many options to choose from, but the ones that we’re concerned with here are SiteUrl and SiteListName. SiteURL is the absolute URL of the site that will contain our list, and SiteListName is the display name of the list. I stress display name as this is different than working with most other APIs for SharePoint, which tend to use the internal name. Also – it’s relatively easy for users to change the display name of the list. Doing so will break the package until it is reconfigured.

image

Next, drag a Script Component onto the canvas, below the data source. If prompted, choose “Transformation” for the script type. Next, connect the two actions by dragging the arrow from the  SharePoint List Source to the Script Component.

image

Next, double click on the script component to bring up the script component editor. From the left, select Input Columns and select all of the columns to use in this script. In our case, we’ll be working with the columns shown below:

image

Next, click on the Script section, choose the language that you want to work with, then click the “Edit Script” button.

image

Without getting into too much detail of how the script action works, what we are going to do is to add code that will run for each row of data that flows through the transformation. In our case, that will be for each configuration item. We’re going to use the values of the columns of each configuration item to render the reports. Therefore, the code that we will write will go into the “Input0_ProcessInputRow” sub.

Before we can do that however, we need to add some supporting items. Firstly, since we’ll be working with web services, we’ll need to reference the .Net System.Web.Services library. Right click on the project name in solution explorer, and select Add Reference. From the .Net tab, select System.Web.Services, and click OK.

image

Next, expand the “Imports” section and import the System.IO and the System.Net  namespace.

image

We now need to add our Reporting Services proxy class. The best way to do this is to first create a new class. Right click on the project in solution explorer, and select Add – Class.

image

Next, give the class a name. I like to match the name to the main class embedded, so the new name is ReportExecutionService.vb. Next, using Notepad, open the file that you created or downloaded in Step 3 above. Select all text, copy it into the clipboard, and then paste it into the newly created class, overwriting anything already there. Once done, save and close the class.

Next, I add a helper function to the script that helps to deal with URLs missing an ending slash. You can add it immediately above the “Input0_ProcessInputRow” sub. The code is below:

 Private Function CheckSlash(ByVal input As String) As String
        If input.EndsWith(Path.DirectorySeparatorChar) Then
            Return input
        Else
            Return input & Path.DirectorySeparatorChar
        End If
    End Function

As we saw below, the output format parameters aren’t the friendliest, and we will need to specify the extension for the output file. To allow this, I also wrote a small helper function to turn output format values into file extensions, and include it below. This also needs to be added to the script.

Private Function GetExt(format As String) As String
        Select Case format
            Case Is = "XML"
                Return "xml"
            Case Is = "Null"
                Return Nothing
            Case Is = "CSV"
                Return "csv"
            Case Is = "ATOM"
                Return "atom"
            Case Is = "PDF"
                Return "pdf"
            Case Is = "HTML4.0"
                Return "htm"
            Case Is = "RGDI"
                Return "gdi"
            Case Is = "MHTML"
                Return "mhtml"
            Case Is = "EXCEL"
                Return "xls"
            Case Is = "EXCELOPENXML"
                Return "xlsx"
            Case Is = "RPL"
                Return "rpl"
            Case Is = "IMAGE"
                Return "tiff"
            Case Is = "WORD"
                Return "doc"
            Case Is = "WORDOPENXML"
                Return "docx"
            Case Else
                Return Nothing
        End Select
    End Function

 

Finally, we’re ready to add code to the “Input0_ProcessInputRow” sub. The complete code listing is below:

  1. Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
  2.       '
  3.       Dim rs As New ReportExecutionService
  4.       rs.Url = "http://home.nautilusinc.local/sites/nmarine/_vti_bin/ReportServer/ReportExecution2005.asmx"
  5.       rs.Credentials = System.Net.CredentialCache.DefaultCredentials
  6.       Dim report As Byte() = Nothing
  7.       Dim deviceinfo As String = Nothing
  8.  
  9.       Dim ParameterPairs As String() = Row.Parameters.Split(";")
  10.       Dim parameters As ParameterValue() = New ParameterValue(ParameterPairs.Length – 1) {}
  11.       Dim CurrentPair As String()
  12.       For i As Integer = 0 To ParameterPairs.Length – 1
  13.           CurrentPair = ParameterPairs(i).Split("=")
  14.           parameters(i) = New ParameterValue
  15.           parameters(i).Name = CurrentPair(0)
  16.           parameters(i).Value = CurrentPair(1)
  17.       Next
  18.  
  19.       Dim historyID As String = Nothing
  20.       Dim credentials As DataSourceCredentials() = Nothing
  21.       Dim showHideToggle As String = Nothing
  22.       Dim extension As [String] = String.Empty
  23.       Dim encoding As [String] = String.Empty
  24.       Dim mimeType As [String] = String.Empty
  25.       Dim warnings As Warning() = Nothing
  26.       Dim reportHistoryParameters As ParameterValue() = Nothing
  27.  
  28.       Dim streamIDs As String() = Nothing
  29.       Dim execInfo As New ExecutionInfo()
  30.       Dim execHeader As New ExecutionHeader()
  31.  
  32.       rs.ExecutionHeaderValue = execHeader
  33.       execInfo = rs.LoadReport(Row.ReportURL, historyID)
  34.       rs.SetExecutionParameters(parameters, "en-us")
  35.       Dim destUrl As String = Row.DestinationLibrary
  36.       Dim destinationUrl As String = CheckSlash(destUrl) + Row.SubscriptionTitle + "." + GetExt(Row.Format)
  37.       Dim r As Byte()
  38.  
  39.       Try
  40.           report = rs.Render(Row.Format, deviceinfo, extension, mimeType, encoding, warnings, streamIDs)
  41.           Dim m_WC As WebClient = New WebClient
  42.           m_WC.Credentials = System.Net.CredentialCache.DefaultCredentials
  43.           r = m_WC.UploadData(destinationUrl, "PUT", report)
  44.       Catch ex As Exception
  45.  
  46.       End Try
  47.   End Sub

 

Again, without getting into too much detail, some explanation of the above code is in order.

Lines 3-5 initialize the web service, assign it a URL (Don’t forget to change this for your environment!!) and assign it the credentials to use when calling the web service.

When this  sub is called by SSIS, it is passed a row object. The row object contains column objects for each column that is used by the script (this was configured above). Therefore, to get the value for any given column, you simply need to refer to it as row.ColumnName. In our case, to get the value of the Parameters column, you use row.Parameters. Lines 9 through 17 get the value of the parameters column, split the value into an array of string objects using a semicolon as a value delimiter, then for each of these objects, separates them into name/value pairs using the equals sign as a delimiter, and them finally assigns them to a Reporting Services parameter collection.

Using this approach, we can use a single field to store all of the parameters for a report, and any report can have any number of parameters.

Lines 19-32 are  primarily used for initialization. Line 33 loads the report specified in the subscription (by calling row.ReportURL). Line 34, sets the parameters, and lines 35-36 set the destination variables.

Finally, Line 40 calls the web service to actually render the report into a byte stream, and line 43 uses the .Net WebClient object to upload the file directly into SharePoint. In this example, we don’t actually add any metadata to the SharePoint library, but if this was required, you could use the techniques outlined in this post. We are now ready to test the process.

Step 5 – Run the Package

Close the Script editor window and click the OK button. If all is well, your Script Component action should show no errors. When ready, click the run button to test your package. If all is well, after a short compilation period, you should see that 2 records were successfully read from the subscription list, an both steps should show green. If things don’t go well, the error messages are pretty good….

image

Navigating to the destination library, we see the two requested reports.

image

Next Steps

Obviously, every time that this package runs, the reports will be overwritten with the new report. This may be desired behaviour, but if not, you may want to turn on version control (each version will be stored as a version) or modify the script to change the file name on each run (date stamping is a common technique).

In addition, you will want the package to be run automatically without human intervention. To do this, you’ll want to deploy it to a SQL Server running SSIS , and to schedule it to run as an agent job. There is a wealth of information online for how to do that.

Conclusion

The example provided above covers a single use case, but with minor adjustment could be used to automate all sorts of reporting tasks. A common one would be to use the NULL renderer to refresh report caches on a server. If you find any unique uses of this approach, I would love to hear about it. Please post a comment!

Credits

In preparing this post, I found the following articles to be useful:

SSIS and Reporting Services Web Services

Uploading documents to WSS (Windows Sharepoint Services) using SSIS

SharePoint reporting services SOAP endpoint in CTP3

Upload document from Local Machine to SharePoint Library using WebService

Uploading files to the SharePoint Document Library and updating any metadata columns

Advertisements

Upgrading SQL Server Reporting Services to 2012 In SharePoint Integrated Mode

That title could actually be longer….

SQL Server 2012 brings with it a number of key Business Intelligence features that apply directly to SharePoint environments. One of the major improvements is the way that Reporting Services installs. Prior to version 2012, when running in SharePoint integrated mode, it installed along side of SharePoint, and connected through an add-in. With 2012, it is now a full SharePoint Service application, with all of the associated benefits that brings.

It is simple enough to set this up on a new farm, but what about organizations that are already using SSRS in integrated mode? Since I was unable to find any prescriptive guidance on the upgrade process, I ran through it on a test farm, and below are my findings. This describes the process of upgrading from SSRS 2008 R2 to the RTM version of SSRS 2012.

SQL Server 2012 has some relatively strict operating system requirements. First and foremost, you need to be running at least Windows 7 or Windows Server 2008 R2 Service Pack 1, or Windows Vista or Windows Server 2008 Service Pack 2. If not, you’ll get the following message immediately.

image

In addition, depending on what you’re upgrading, it’s pretty fussy about your source environment as well. For example, if you’re upgrading management tools or BIDS, and you already have Visual Studio 2010 installed, it will need to be at least at the Service Pack 1 level. Your source SQL Server also has specific service pack requirements. The complete supported upgrade matrix can be found here. Unfortunately, if these requirements are not met, the installation will fail much further along in the process, and you’ll need to repeat several steps after correcting.

Once the SQL Server Installation Center launches, you’ll want to pick the Installation tab, and then the Upgrade option.

image

After a few steps, you’ll encounter one of the new screens pertaining to Reporting Services.

image

Previously, the installer was totally unaware of Integrated Mode Reporting Services.  You would use SSRS configuration to set it up, but now the upgrade wizard, as well as the full product installer, is fully aware of Integrated Mode.

When performing the upgrade, the installer will go ahead and create the SharePoint service application for you. This is different than when you perform a fresh install – in  that case you manually create the service application after installation. However, in order to do so, it needs to create an application pool for the service application, and you will be prompted for the credentials of that pool.

image

After several more standard screens, the upgrade rules will be run. This is where you will find out if you are missing a prerequisite, or it is not at the required patch level. However, if all is good, all of the rules should show as Passed, with the exception of “Direct Browsing to Report Server”, which will show a warning.

image

Previously, if you knew the correct URL, you could navigate directly to Reporting Services and the reports stored within SharePoint through a very rudimentary interface. This warning is simply alerting you to the fact that this is no longer an option with 2012.

The remainder of the installation is straightforward. When done (and if successful), you can navigate to the Service Applications section of Central Administration. There, you should see the new SSRS application.

image

I’m not a big fan of the name that the default upgrade uses for the application, but that’s simple enough to change. The important thing to note is that all of your subscriptions, snapshots, etc, will have migrated over. The upgrade upgrades the two Reporting Services databases (ReportServer, and ReportServerTempDB by default), and adds a new one, ReportServer_Alerting, which are all used by the service application.

image

In order to enable data alerts and subscriptions, a number of security modifications need to be made to the SQL Server. In addition, the SQL Server Agent must be running to use these features. Editing the Service Application shows a screen that has a link to Provision Subscriptions an alerts. Clicking through it reveals the following screen:

image

The View Status section simply gives you an indication of whether or not the Agent is running on the server, but clicking the “Download Script” button will give you a SQL script that will set up the required roles and permissions on your SQL Server. This script must be run on the SQL Server that holds the Reporting Services databases. In order to run it, simply open up SQL Server Management Studio, connect to the server, and click the New Query button. Once the query window opens, paste the query in, and run it (the Execute button).

image

Finally, enter the credentials for SharePoint to connect to your SQL Server Agent, and click OK. Once done, you’ll be in a position to use the new features available in Reporting Services, and all of your existing investments in reports should continue to operate as before.

For a major architectural change, this is actually a pretty smooth transition.

As I post this, it’s quite early in the life of 2012, so I would be quite interested to hear of any other experiences or gotchas. If there’s something that I should add to this post, please post a comment, I’d love to hear about it.

TechNet Radio Community Corner Interview

Last November, I was interviewed by Sr. Technical Evangelist John Weston on the MVP program, Office 365, Cloud Computing, Business Intelligence, and how these things all tie together. The entire interview was conducted online using Lync online, available in Office365. It’s now online, and can be seen below.

You can see other Technet Radio episodes by visiting the Edge site here

Using SQL Server Report Builder with Internet Explorer 9

One of the unsung heroes of the Microsoft Business Intelligence stack is Report Builder. Report Designer has been part of Business Intelligence Development Studio (BIDS) for quite some time, but BIDS is more of a designer tool. In order to get report design into the hands of power users, Microsoft provided Report Builder initially with a reduced set of functionality SQL Server 2005, but with Report Builder 3.0 which ships with SQL Server 2008 R2, it’s just as capable as BIDS. When running in SharePoint Integrated mode, you can design reports as if they were any other type of Office document.

Unfortunately, if you edit a report, you may be greeted with the message: “To use Report Builder, you must install .Net Framework 3.5 on this computer.”

image

Report Builder does require .Net Framework 3.5, but you’ll see this message even when you have it installed. Clicking on the “Install .Net 3.5” will reinstall it, but won’t help.

This only happens when using Internet Explorer 9, and is due to the fact that it doesn’t correctly detect the Framework’s presence. This doesn’t happen with any other browser, including previous versions of IE. It can be worked around by setting the browser’s compatibility mode.

To do that, either press the F12 key, or turn on developer tools from IE’s Tools menu:

image

Doing so will bring up the developer tools window, where you can set the broswer mode. Setting it to anything other than IE9 will work.

image

Just the act of setting it should start the Report Builder download process. The setting will persist for the life of the browser window, so you’ll need to do it again the next time that you edit the report.

I’d love to hear of any better fixes to the problem, but for now, this lets you get the job done.

Upcoming Speaking Engagements – Spring 2012

While the blog has been quieter than usual for the past couple of months, owing to a hectic schedule, I do have a number of speaking engagements coming up that I wanted to promote. All of them are related to SharePoint Business Intelligence in one way or another, and all are in the Toronto/South Western Ontario area. If you’d like to come out and talk SharePoint, Business Intelligence, or just indulge in a SharePint or two, I would love to see you there.

Hamilton SharePoint User Group
Thursday, Feb 16 2012
SQL Server Reporting Services with SharePoint
SQL Server Reporting Services provides a rich reporting environment, and integration with SharePoint makes it seem seamless to end users. How does Reporting Services impact the SharePoint environment? This presentation will walk through the basic features of Reporting Services, and architectural considerations when installing in a SharePoint farm. In addition, some of the differences included in the upcoming SQL Server 2012 version of Reporting Services will be discussed.
 
Toronto SharePoint User Group
Wednesday, April 18, 2012
What’s new in SQL Server 2012 for SharePoint
SQL Server 2012 brings a wealth of new features to the core database used by SharePoint. However, it also brings a number of exciting new Business Intelligence features right to your SharePoint users.

This session will walk through a number of the new features that have a direct impact on SharePoint administrators, designers and end users, with in depth demonstrations of how to configure and use them. These features include significant architectural changes to Reporting services, the new BISM or tabular engine for Analysis Services and PowerPivot, and the new end user focused reporting tool, PowerView.

SharePoint Summit
May 15, 2012
Reporting for Duty – Best Practices for Reporting Services with SharePoint

 

Reporting Services and SharePoint have been working together since SharePoint 2003. SQL Server 2005 SP1 brought the ability to use Reporting Services in Integrated Mode through a SharePoint Add in, and with SQL Server 2012, Reporting Services is a fully fledged SharePoint Service Application, and some features, the new PowerView in particular, are only available through SharePoint Integrated Mode.

Reporting Services bring a wealth of benefits to your SharePoint farm, but can also have a significant impact on it. This session will discuss the do’s and don’ts for a successful Reporting Services implementation. It will cover architectural considerations through to Report design, for both the Reporting Services Add In (SQL Server 2005-2008) and the new Reporting Services Service Application (SQL Server 2012).