RSS In, Reporting Services Report Out

RSS (Really Simple Syndication) is a popular standard for summarizing content-driven Web sites, such as news outlets and Weblogs. Typically referred to as an RSS feed, the RSS file contains XML-formatted headlines and descriptions of content items on the site. The standardized format and availability of RSS feeds has driven their popularity. In addition, most RSS is automatically updated when the site content changes. This makes it easy to build applications and Web sites that provide real-time links to RSS-published content. An aggregator is a Web site or application that collects and displays RSS feeds.

SQL Server Reporting Services (SSRS) is an integrated tool that allows you to create Web-based reports. This example assumes that you have SQL Server Reporting Services installed and configured on an available server.

SSRS is not configured to consume SSIS data by default. For instructions on how to configure Reporting Services to consume Integration Services data, search the MSDN Library for "Configuring Reporting Services to Use Integration Services Package Data."

In Business Intelligence Development Studio or Visual Studio, start a new Business Intelligence/Integration Services project. Drag a new Data Flow task onto the Control Flow tab. On the Data Flow tab, drag an XML Source, Term Extraction, and DataReader Destination.

Configure the XML Source by double-clicking it. On the XML Source Editor screen, supply the URL to an RSS feed document in the Connection Manger page, such as www.sqlservercentral.com/sscrss.xml. The following is an example of some of the XML in an RSS feed:

 <?xml version="1.0" ?> <rss version="2.0"> <item>   <title>Executing the result set</title>   <description>SQL Server Database administrators often generate SQL Statements and execute the generated SQL statement in order to simplify certain tasks. It has always been a twin operation. This article illustrates how to use un-documented stored procedures to execute the generated SQL Statements directly.</description> <guid>www.sqlservercentral.com/articles/articlesexternal.asp?articleid=2036</guid>   <pubDate>Mon, 29 Aug 2005 00:00:00 GMT</pubDate> <link>http://www.sqlservercentral.com/articles/articlesexternal.asp?articleid=2036 </link> </item> <item>   <title>Generating Test Data with Integration Services</title>   <description>DTS was one of the most amazing new features of SQL Server 7 and in SQL Server 2005 it has been renamed to Integration Services. This component has some incredible new capabilities, many of which come at a steep learning curve. New author Kristian Wedberg brings us a basic article and some code on how you can SSIS to generate test data.</description> <guid>http://www.sqlservercentral.com/columnists/kwedberg /generatingtestdatawithintegrationservices.asp</guid>   <pubDate>Mon, 29 Aug 2005 00:00:00 GMT</pubDate> <link>http://www.sqlservercentral.com/columnists/kwedberg/ generatingtestdatawithintegrationservices.asp</link> </item> 

Click the Generate XSD button to automatically generate the XML schema definition document, as shown in Figure 17-1. You can store the XSD file anywhere, but it is a good practice to store related project files together.

Figure 17-1

Note

An XML Schema provides a definition of the expected data format in an XML document.

On the Columns page, select "item" from the Output drop-down list and check the description field in the Available External Columns table. This defines mapping between the contents on the description field and an output column named Description. On the Error Output page, select Ignore failure for all Error and Truncation conditions. Click OK to close the XML Source configuration editor.

Drag a precedence constraint (represented by a green connection arrow) from the XML Source task to the Term Extraction task. Select the item output as shown in Figure 17-2 and click OK to define the transformation.

Figure 17-2

Double-click the Term Extraction task to configure it. Check the description column in the Available Input Columns table on the Term Extraction tab, as shown in Figure 17-3. Click the Configure Error Output button and set the Error drop-down list to Ignore failure. Click OK to close the Error Output screen, and then click OK again to close the Term Extraction Editor.

Figure 17-3

Note

The Term Extraction task is described in Chapter 4. You will recall that it outputs two columns: term and score. Term contains a list of repeated terms in the input. Score contains the number of times each term appears in the input.

Drag a precedence constraint from the Term Extraction task to the DataReader Destination task. Define a Data Viewer on this precedence constraint by right-clicking it and clicking Data Viewers from the context menu. Click Add and then OK to accept the default grid Data Viewer configuration.

Configure the DataReader Destination by double-clicking it to open the Advanced Editor for the Data Reader Destination. Change the Name property to RSSFeedOutput. Click the Input Columns tab and check the Term and Score items in the Available Input Columns table as shown in Figure 17-4. Click OK to exit the DataReader Destination Advanced Editor, and then click OK again to exit the Data View Path Editor dialog box.

Figure 17-4

Save your work and test it by clicking the Play button. The Data Flow executes and displays results in the Data Viewer as shown in Figure 17-5. Click the Play button on the Data Viewer. The package will continue executing in debug mode until execution completes (this may take some time). After the objects on the screen turn green, indicating that the tasks have completed, halt execution by clicking the Stop button.

Figure 17-5

Add a new Reporting Service project by clicking File Add New Project. Click Business Intelligence Projects and select Report Server Project from the New Project dialog box. Name the project RSS_Feed_Report.

Right-click the Reports folder, and then click Add New Report to create a new report. Define a new data source for the report, selecting SSIS as the data source type. Name the data source RSS_Feed_SSIS. Enter the connection string manually. Enter -f for a file path, followed by the full path in double quotes to the package file. Check the Make This a Shared Data Source checkbox as shown in Figure 17-6.

Figure 17-6

Click the Credentials tab and select the No Credentials option. Click Next and enter the name of the DataReader Destination in the Query String text box, as shown in Figure 17-7.

Figure 17-7

Note

The DataReader Destination is more than just a new feature; it is a new type of feature for SSIS developers. This is apparent at this step in the example as you reference the name of an object in a Query string.

The wizard screens that follow allow you to select report type and choose fields, field hierarchy, and table style, but no changes will be made to them. Click through them to the Completing the Wizard screen to continue. The last screen displays the selected configuration options and allows you to name the report as shown in Figure 17-8. Name the report RSS_Feed_Report and click Finish to complete the wizard.

Figure 17-8

Note

For more information on SQL Server Reporting Services, see Professional SQL Server Reporting Services by Paul Turley, Todd Bryant, James Counihan, George McKee, and Dave DuVarney (Wiley, 2004).

Click the Preview tab to view the report as shown in Figure 17-9. Your results will vary, based on the terms extracted when you execute the report.

Figure 17-9

Another use for term extraction reports like the one you just developed is text mining. One application of text mining is gathering statistics on technical support messages and calls.

If the example above were adapted so that the XML input contained a summary of technical support issues, the report would reflect trouble-call trends. This data could then be used by a support center to schedule support personnel or by the manufacturer to identify areas for product improvement.

Other applications of text mining include Weblog, newsgroup, or news site aggregation to detect trends in topics or reader comments.