Udostępnij za pośrednictwem

Consuming SSIS package data in Reporting Services (and using Web Services in addition) Part 2

  • Artykuł

  (Update: Due to security reasons the functionality retrieving data from SSIS packages has been deprecated and is no longer available in SQL Server 2008 R2)

(Sample project attached to the blog entry !)  

Having stated the need for getting data from SSIS packages in Reporting Service in my last post (Part 1), we now want to concentrate how to achieve the implementation. SQL Server Integration Services is not by default accessible from Reporting Service. You will have to do some preparations in order to make it visible through the Reporting Designer surface in Visual Studio. The following article describes the directions how to do that in a good detail:

Configuring Reporting Services to Use SSIS Package Data

(Watch out for configuration traps here)

Boiled down, the RSReportDesigner.config will have to be configured to offer the option in Visual Studio. After changing the settings, make sure to restart Visual Studio and to apply the changes made to the configuration file.

After that you will be able to find the SSIS option in the DropDownBox of the Report database properties window:

image

(The Connection string reads: –f C:\Projects\Blogs\FY09\GetStockQuotes\Package.dtsx)

Getting data and combing information in SSIS from multiple data sources

To combine most of the technologies I created the following scenario which can be also downloaded in the solution file attached to the blog post. As being not directly accessible a back book system in a bank is producing portfolio information and send them to plain text files. As for some reason the one system only has the WKN and the other back book system has the ticker meta data information (which is also available in a flat file), the data will have to be mapped together before actually getting the quotes from the external source. After having done the Merge join to get the information together, an external service is called for getting real time information data for the quotes. (In this scenario I used an external service Xignite which can be used within a free trial) After getting the quotes, the data will be send to a DataReader destination where it can be consumed by external components like .Net Framework application and Reporting Services. You can also run the SSIS package alone just for test reasons and to get familiar with SSIS working with WebServices. I added additional tracing information to the pipeline to be able to see the time needed to request the information from the service.

If you are not familiar with getting information from web service within SSIS, see the following article from Jamie with an excellent video which will guide you from end to end and see how the package here was done.

The following picture shows the simple data flow task used in the solution.

image

After having done the SSIS part you can create the report using the data from the SSIS package. As mentioned above this is also a straight forward process if you enabled the SSIS extension as a data source in the Reporting Services config files. The connection string for the SSIS package is the patht of the SSIS package.

After that you can walkthrough creating a data source, seeing the information in the dataset viewer and drag and drop the information to the report. Be aware that due to the extra transformations and requests to the cloud, the report might take a bit longer than loading directly the information from a local data source like a SQL Server database but will give you the flexibility to integrate information from the web in your Reporting Services reports.

The simple reports looks like the following in Visual Studio Designer:

image

For you to make the sample work in your environment, make sure you change the following settings to your environment:

Open the SSIS package:

  • Change the Loginname and the Password for the StockService (If you do not have a login yet, you can get a free trial from the website). The credential information is saved in variables (which also can be passed to the SSIS package not to store them permanently in the package)

image

  • Change the Directory to the flat files provided. These files exists in the solution containing sample data and be referenced from the SSIS package.

Open the Report:

  • Change the path to the SSIS package from the Shared Data source component

image

Done. Now you should be able to see the same design (Sure with another data as quotes will change :-) )

If you have any question or something is not clear about this approach, feel free to contact me.

 

 

-Jens

SSIS_Meets_RS.zip

Comments