Generate SSRS Reports in SSIS – Control Flow – 2 of 4

Download Source Code: Here

In the previous post Generate SSRS Reports in SSIS – The Report – 1 of 4 we developed an SSRS report that we will now call from within our SSIS package.  We will also continue to use the same solution files as we did in the first post.

The Package

First add a new Integration Services project to the existing solution, call the project SSIS.

A package is added to the solution by default.  Rename this package GenerateInControlFlow.dtsx.

The following table details all variable that you need to create for this package.  Take special care to ensure that the EvaluateAsExpression properties of FileName and FullFilePath variables are marked as True, and that the expressions are used in the Expression property.

The FileName expression creates a file name based on the ShippingDate and the CustomerID in the format 20030727_AW00011001_TransactionReport.pdf

You also need to create a folder somewhere on your machine to store the generated reports.  In my example this folder is at C:\Temp\Files\ this file path is stored in the FilePath variable.

The FullFilePath variable combines the FilePath and the FileName together.

Name	Data Type	Value	Evaluate AsExpression	Expression
CustomerID	String	AW00011001	False
ShippingDate	DateTime	2003/07/27	False
FileType	String	PDF	False
FileExtension	String	.pdf	False
FileName	String		True	(DT_WSTR,4)YEAR( @[User::ShippingDate]) + RIGHT("0" + (DT_WSTR,2)MONTH( @[User::ShippingDate] ),2) + RIGHT("0" + (DT_WSTR,2)DAY( @[User::ShippingDate]),2) + "_" + @[User::CustomerID] + "_TransactionReport" + @[User::FileExtension]
FilePath	String	C:\Temp\Files\	False
FullFilePath	String		True	@[User::FilePath] +  @[User::FileName]
Records	Object		False

Create a connection to the AdventureWork2008DW database.  Add an Execute SQL Task, For Each Loop Container and Script Task.

In the SQL task we are going to use a SQL script to get a list of all the CustomerID’s that had completed transactions on a specific ShippingDate.  We pass the shipping date into the SQL Script using our ShippingDate variable.  Let setup the SQL Task.

Change the connection type to OLE DB, Choose the connection that we setup earlier to the AdventureWorks2008DW.  Change the SQLSourceType to Direct Input and the ResultSet property to Full result set.

Use the following SQL in the SQLStatement property, notice the use of the ? in the join condition.  Well be mapping this to the ShippingDate variable, just now.

SELECT DISTINCT  
   DimCustomer.CustomerAlternateKey AS CustomerID  
FROM

   dbo.FactInternetSales  
INNER JOIN DimCustomer  
   ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey  
INNER JOIN DimDate  
   ON DimDate.DateKey = FactInternetSales.ShipDateKey  
   AND DimDate.FullDateAlternateKey = ?

On the parameter mapping tab choose the variable name User::ShippingDate, set the Data Type to DATE and the Parameter Name = 0

On the Result Set tab, set the Result Name = 0 and the variable name to User::Records, this will load the results of the SELECT statement into our Records variable.

Right click on the Execute SQL Task.  You should see the task goes green with the successful completion of the task.

Next we need to configure the For Each Loop container.  On the Collection tab choose the foreach ADO Enumerator, and select the User::Records variable.

 

Now all we need to do is configure the script task to generate the report.  Open the script task and add the following variables to the ReadOnlyVariable property.

User::CustomerID, User::ShippingDate, User::FileExtension, User::FileName, User::FilePath, User::FileType, User::FullFilePath,

 

Enter into the script editor, by clicking the Edit Script button, Ensure that you have choose visual basic as your scripting language.

Now we need to add a reference to the SSRS webservice that generates the SSS reports.  In the solution explorer, right click on the folder called “web reference”, and select Add Web Reference.  In the search box that appears enter the following url to find the web service:

http://localhost/reportserver/reportexecution2005.asmx

Change the web reference name to ReportServer, and click Add Reference.

Back in the main script editor, you need to add two namespaces to your script.  The first is System.IO which allow you to read and write to files streams.  The second is a reference to the actual project, for this you need to copy the GUID from the project explorer window (see image below).

Imports System.IO

Imports ST_cb1ed882aced43b28c27bb3f21920beb

Below is the code used in the sub main procedure.  You can copy and paste the following code into the script editor window.

   
    Public Sub Main()



        Dim objRSExec As New ReportServer.ReportExecutionService

        Dim objParamCustomerID As New ReportServer.ParameterValue

        Dim objParamShippingDate As New ReportServer.ParameterValue

        Dim objParams(1) As ReportServer.ParameterValue



        Dim objResult() As Byte

        Dim objStream As FileStream



        Dim strFileType As String

        Dim strFileExtension As String

        Dim strFullPathName As String



        strFileType = Dts.Variables("User::FileType").Value

        strFileExtension = Dts.Variables("User::FileExtension").Value

        strFullPathName = Dts.Variables("User::FullFilePath").Value



        With objParamCustomerID

            .Name = "CustomerID"

            .Value = Dts.Variables("User::CustomerID").Value

        End With



        With objParamShippingDate

            .Name = "ShippingDate"

            .Value = Convert.ToDateTime(Dts.Variables("User::ShippingDate").Value)

        End With



        objParams(0) = objParamCustomerID

        objParams(1) = objParamShippingDate



        With objRSExec

            .Credentials = System.Net.CredentialCache.DefaultCredentials

            .Url = "http://localhost/reportserver/reportexecution2005.asmx"

            .LoadReport("/GenerateSSRSReportsInSSIS/Customer Transaction Statement", 
Nothing)

            .SetExecutionParameters(objParams, "en-us")

            objResult = .Render(strFileType, Nothing, strFileExtension, Nothing, 
Nothing, Nothing, Nothing)

        End With



        objStream = File.Create(strFullPathName, objResult.Length)

        With objStream

            .Write(objResult, 0, objResult.Length)

            .Close()

        End With



        Dts.TaskResult = ScriptResults.Success

    End Sub



End Class

You should now be able to run the package and it will create separate PDF documents in C:\Temp\Files.

If you’d rather you can change the format in which the report is rendered, if you do remember that you need to change FileType and FileExtension variables.  The table below shows the acceptable combinations:

FileType	FileExtension
IMAGE	.jpg or .tiff
PDF	.pdf
WORD	.doc
EXCEL	.xls
CSV	.csv
XML	.XML
HTML4.0	.html
MHTML	.mhtml