Generate SSRS Reports in SSIS – The Report – 1 of 4

Download Source Files: Here

This is the first of four posts looking at how to generate and save SSRS reports from within SSIS. 

In this post I will look at creating the stored procedure to retrieve the report information, and creating and deploying the sample report to the report server.

The Second post will look at a method of calling to the report server and generating reports in the control flow of SSIS.  We’ll look at how to connect to the web service, pass parameters and save a report to disk.

The third post in this series will show how to do the same thing in Data Flow, but instead of writing the reports out to disk I’m going to save the reports in a binary format straight to the database. 

The fourth post in this series will show you how to use SSIS to retrieve the binary files saved in SQL server and render them back to disk.  I’ll also illustrate in this example how you can attach the files to an email and send them.

I hope that at the end of this series you’ll have the knowledge to create your own communications engine, that can generate, archive and distribute statements to their intended recipients.

The Stored Proc

For this example I’m going to be using the AdventureWorkDW2008 database.   I’m going to take a typical example of a client statement for the content of our report.  I’m going to use a stored proc to retrieve all the transactions for a specific customer based on when the customers order was shipped.  The stored proc will take two input parameters the CustomerID and the ShippingDate of their order.

USE AdventureWorksDW2008;

GO



IF OBJECT_ID('dbo.spCustomerTransactions',N'P')  IS NOT NULL

DROP PROCEDURE spCustomerTransactions

GO



CREATE PROC spCustomerTransactions(@CustomerID AS NVARCHAR(15),  
@ShippingDate AS DATE )

AS

SELECT  
   CustomerAlternateKey,

   ISNULL(DimCustomer.Title + ' ' ,'')  
   + ISNULL(DimCustomer.FirstName + ' ','')

   + ISNULL(DimCustomer.MiddleName + ' ','')

   + ISNULL(DimCustomer.LastName,'') AS FullName,

   DimProduct.EnglishProductName,

   DimDate.FullDateAlternateKey,

   FactInternetSales.SalesOrderNumber,

   FactInternetSales.OrderQuantity,

   FactInternetSales.UnitPrice,

   FactInternetSales.SalesAmount

FROM

   dbo.FactInternetSales

INNER JOIN DimCustomer

   ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey

   AND DimCustomer.CustomerAlternateKey = @CustomerID

INNER JOIN DimProduct

   ON DimProduct.ProductKey = FactInternetSales.ProductKey

INNER JOIN DimDate

   ON DimDate.DateKey = FactInternetSales.ShipDateKey

   AND DimDate.FullDateAlternateKey = @ShippingDate

GO



EXEC spCustomerTransactions @CustomerID = 'AW00011001', @ShippingDate = '2003-07-27';

You should see the following results

The Report

This post is not really about how to create SSRS reports so we are going keep it simple and use the report wizard to create our report. 

First start up Visual Studio, create a project called GenerateSSRSReportsInSSIS.  Tick the create directory for solution box, name the solution SSRS.

Create a new Shared Data Source called AdventureWork2008DW which connects to the AdventureWork2008DW database.

Next add a report to the solution called Customer Transaction Statement.  On the Reports folder click Add Report and the report wizard should start up.

The data source that we just created should be selected by default, click next.

Use the stored proc that we created earlier, click next.

EXEC spCustomerTransactions @CustomerID = 'AW00011001', @ShippingDate = '2003-07-27'

 

Choose tabular, click next.

Now we need to place the fields in the correct sections of the report to give it a reasonable appearance.  Look at the picture below to see where to put the fields, then click next.

Choose stepped, click next.

Choose slate, click next.

Now name the report Customer Transaction Statement, click next.

The report should look something like this, I’ve made some adjustment to the columns and made the page slightly wider, although this is aesthetics only.

Next change the page setup, change the Margins to 0.25 inches, this will ensure that when we generate the report as pdf later that all the data will fit on one page.

Run the report and see what it look like.

In order to run the report for multiple customers we are going to have to parameterise the report.  Click on the Parameters folder in the Report Data tab.

Add a parameter called CustomerID or type Text.

Add a second parameter called ShippingDate of type DateTime.

 

Click on DataSet1 in the Report Data tab.  Change the query type to Stored Procedure and choose spCustomerTransactions from the drop down.

Click on the parameters tab, the variables that we just created will display in the parameter value drop down box.  Match these up with the parameter values already identified in the stored proc.

Preview the report again, this time you will be prompted for the CustomerID and the ShippingDate.  You can continue to use AW00011001 and 2003-07-27.

 

The report is now finished, all we need to do now is publish this to the report server.

Right Click on the SSRS project and select Properties in the TargetServerURL type the name of the report server instance that you want to deploy to.  If you kept the default installation options when installing Reporting Services then the report server should be at  http://localhost/reportserver

Right click on the report in the solution explorer and click deploy.

Next open Internet Explorer and navigate to the report manager, again the default installation will have this at http:/localhost/reports. 

You should see a folder for our solution, GenerateSSRSReportsInSSIS, Click on this folder.

You should now see our report Customer Transactions Statement, Click on this

Now run the report as before

Now that the report is on the reporting server we will be able to call this from our SSIS project that we are going to create in the next post.