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.

image

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  

image

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

image

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.

image

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



image





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.



image



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.



image 



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,



 image



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.



image



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




image



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

Comments

  1. How can we store the report in the db instead?

    ReplyDelete
  2. I was searching all over for an example like this, and nothing was as clearly written and easy to modify than this. THANK YOU!

    ReplyDelete
  3. Good Stuff Douglas thank you. Have you published parts 3 and 4 yet?

    ReplyDelete
  4. Genuinely it superb blog in my opinion so thanks for sharing it, i will be thankful to this.

    ReplyDelete
  5. Very helpful. I have an issue that when I run the script in SSIS it opens the Visual Studio Shell. Why does this happen?

    ReplyDelete
  6. Has anyone tried this in 2012, with .net 4.0 framework? I am getting errors that things like .credentials and .url don't exist. Any help is much appreciated.
    Thanks,

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Paula,
    U might not have added correct web reference,it worked on 2012

    Many Thanks
    Chandu

    ReplyDelete
  9. Thank you very much for this post Douglas Day. I have been struggling to achieve this task from one week.

    Keep posting such helpful posts.

    Thanks,
    Rahul.

    ReplyDelete
  10. Hi, I am trying to work with SQL 2012 and getting Binary code not found while just adding the web reference. Can you please help me ?

    Regards

    Ayyanar

    ReplyDelete
  11. Hi Guys,

    Its working the following two settings needs to be for SQL 2012/2014

    1. On the main menu, change the debug to ‘Release’
    2. Go to Build Properties of the Solution and Turn Off the ‘Generate Serialization Assembly’

    Regards

    Ayyanar

    ReplyDelete
  12. I'm using the 2010 shell, and had to add the reference in a different way in order to enable the 2.0 framework - and make the object types and methods described above available.
    1. Right-click the project node and select "Add Service Reference..."
    2. At the bottom of the dialog box, click "Advanced"
    3. At the bottom of the second dialog, click "Add Web Reference".

    See http://msdn.microsoft.com/en-us/library/bb628649.aspx

    ReplyDelete

Post a Comment

Popular posts from this blog

SSIS - Google Spreadsheets Data Source

Exam 70-443 – Overview