none
Running an SSRS report from SSIS

    Question

  • I want to know how I can run an SSRS report from an SSIS package. I need to run two reports for 30 locations. I need to format the reports in Excel and put both reports in the same Excel file on separate tabs. I also need to put the location name in the name of the Excel file.
    Thursday, August 20, 2009 11:27 PM

All replies

  • Yes, you can do this. There are several tasks you asked:
    1) Run SSRS report from SSIS package
    2) Format the reports in Excel
    3) Put the Excel files on Separate tabs
    4) Put the Excel file with the path information.

    There are two options:
    1) Use Web Service Task to call SSRS webservice to generate reports in Excel format.
    2) Or Create a App, App will call SSRS to generate the report, and use Execute Process Task to invoke this App.
    3) Create the Data Drive reports in SSRS and delivery the Excels to particular folder.
    4) Use SSIS to combine the excel files to excel sheet.

    Thursday, August 20, 2009 11:45 PM
  • Micror - I'm trying to do the same thing... I had found a code snipet, but it appears I'm getting a timeout on the webresponse.  I've put some msg boxes (as you can see) in my code, and it never hits step 6.  I ultimately get a file with 0 bytes in it.  Any assistance would be greatly appreciated.


    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic 2008.

    ' The ScriptMain is the entry point class of the script.

    Imports

     

    System

    Imports

     

    System.IO

    Imports

     

    System.Data

    Imports

     

    System.Math

    Imports

     

    Microsoft.SqlServer.Dts.Runtime

    Imports

     

    System.ComponentModel

    Imports

     

    System.Diagnostics

    <System.AddIn.AddIn(

    "ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

    <System.CLSCompliantAttribute(

    False)> _

    Partial

     

    Public Class ScriptMain

     

    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

     

    Enum ScriptResults

    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

     

    End Enum

     

    Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)

     

    Dim loRequest As System.Net.HttpWebRequest

     

    Dim loResponse As System.Net.HttpWebResponse

     

    Dim loResponseStream As System.IO.Stream

     

    Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)

     

    Dim laBytes(1024) As Byte

     

    Dim liCount As Integer = 1

     

    Try

    MsgBox(

    "1 - Start of Try Loop")

    loRequest =

    CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)

    MsgBox(

    "2 - loRequest")

    loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials

    MsgBox(

    "3 - loRequest Credentials")

    loRequest.Timeout = 60000000

    MsgBox(

    "4 - loRequest Timeout")

    loRequest.Method =

    "GET"

    MsgBox(

    "5 - loRequest GET")

    loResponse =

    CType(loRequest.GetResponse, System.Net.HttpWebResponse)

    MsgBox(

    "6 - loResponse")

    loResponseStream = loResponse.GetResponseStream

    MsgBox(

    "7 - Here I am")

     

    Do While liCount > 0

    MsgBox(

    "8 - While Loop")

    liCount = loResponseStream.Read(laBytes, 0, 1024)

    loFileStream.Write(laBytes, 0, liCount)

     

    Loop

    loFileStream.Flush()

    loFileStream.Close()

     

    Catch ex As Exception

     

    End Try

     

    End Sub

     

    ' The execution engine calls this method when the task executes.

     

    ' To access the object model, use the Dts property. Connections, variables, events,

     

    ' and logging features are available as members of the Dts property as shown in the following examples.

     

    '

     

    ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value

     

    ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)

     

    ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)

     

    '

     

    ' To use the connections collection use something like the following:

     

    ' ConnectionManager cm = Dts.Connections.Add("OLEDB")

     

    ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"

     

    '

     

    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

     

    '

     

    ' To open Help, press F1.

     

    Public Sub Main()

     

    Dim url, destination As String

     

    ' destination = Dts.Variables("varDestinationPath").Value.ToString + "\" + Dts.Variables("varRSParameter").Value.ToString + " " + Format(Now, "yyyyMMdd") + ".pdf"

     

    ' url = "http://localhost/ReportServer?/ReportExport/Report - Get it from SSIS&rs:Command=Render&SSIS_Parameter=" + Dts.Variables("varRSParameter").Value.ToString + "&rs:Format=PDF"

     

    If Directory.Exists(Dts.Variables("PDFDirectory").Value.ToString + Dts.Variables("UserName").Value.ToString.Trim()) Then

     

    Else

    Directory.CreateDirectory(Dts.Variables(

    "PDFDirectory").Value.ToString + Dts.Variables("UserName").Value.ToString.Trim())

     

    End If

    destination = Dts.Variables(

    "PDFDirectory").Value.ToString + Dts.Variables("UserName").Value.ToString + "\" + Format(Now, "yyyyMMdd") + " - " + Dts.Variables("Tenant").Value.ToString + " - " + Dts.Variables("Project").Value.ToString + " - " + Dts.Variables("RecoveryCategory").Value.ToString + " - Selection Reports.pdf"

    url = Dts.Variables(

    "ReportServerURL").Value.ToString + "CAMSupportSelection&Tenant=" + Dts.Variables("Tenant").Value.ToString + "&Project=" + Dts.Variables("Project").Value.ToString + "&RecoveryCategory=" + Dts.Variables("RecoveryCategory").Value.ToString + "&rs:Command=render&rs:Format=pdf"

     

    'MsgBox(destination)

    MsgBox(url)

    SaveFile(url, destination)

     

    destination = Dts.Variables(

    "PDFDirectory").Value.ToString + Dts.Variables("UserName").Value.ToString + "\" + Format(Now, "yyyyMMdd") + " - " + Dts.Variables("Tenant").Value.ToString + " - " + Dts.Variables("Project").Value.ToString + " - " + Dts.Variables("RecoveryCategory").Value.ToString + " - Selection Reports.xls"

    url = Dts.Variables(

    "ReportServerURL").Value.ToString + "CAMSupportSelection&Tenant=" + Dts.Variables("Tenant").Value.ToString + "&Project=" + Dts.Variables("Project").Value.ToString + "&RecoveryCategory=" + Dts.Variables("RecoveryCategory").Value.ToString + "&rs:Command=render&rs:Format=excel"

     

    'MsgBox(destination)

    MsgBox(url)

    SaveFile(url, destination)

    Dts.TaskResult = ScriptResults.Success

     

    End Sub

    End

     

    Class

    Tuesday, September 01, 2009 8:13 PM
  • Joe, how long does the report take if you run it directly?
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Tuesday, September 01, 2009 11:39 PM
  • Hi Joe, please go through my blog post on how to execute SSRS report from SSIS package.

    http://msbimentalist.wordpress.com/2011/12/27/execute-ssrs-report-from-ssis-package/

    Thursday, June 06, 2013 5:27 AM