Run and Export SSRS Reports from SSIS (SQL Server 2014)

Package “Run and Export SSRS Reports from SSIS (SQL Server 2014)” with export to excel and pdf.

Download Report Export 2014

(please change name of downloaded file from reportexport2014-zip.doc to reportexport2014.zip).

Description is here.

Advertisements
This entry was posted in SSIS. Bookmark the permalink.

7 Responses to Run and Export SSRS Reports from SSIS (SQL Server 2014)

  1. Yik Wing says:

    Hi Zoran,

    Is it possible that you can post the codes of the script task part here?

    Thanks,

    Yik Wing

    • Zoran Ivić says:

      here we go:
      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(256) As Byte
      Dim liCount As Integer = 1
      Try

      loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
      loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
      loRequest.Timeout = 600000
      loRequest.Method = “GET”
      loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
      loResponseStream = loResponse.GetResponseStream
      Do While liCount > 0
      liCount = loResponseStream.Read(laBytes, 0, 256)
      loFileStream.Write(laBytes, 0, liCount)
      Loop
      loFileStream.Flush()
      loFileStream.Close()
      Catch ex As Exception
      End Try
      End Sub

      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”
      SaveFile(url, destination)

      destination = Dts.Variables(“varDestinationPath”).Value.ToString + “\” + Dts.Variables(“varRSParameter”).Value.ToString + ” ” + Format(Now, “yyyyMMdd”) + “.xls”
      url = “http://localhost/ReportServer?/ReportExport/Report – Get it from SSIS&rs:Command=Render&SSIS_Parameter=” + Dts.Variables(“varRSParameter”).Value.ToString + “&rs:Format=EXCEL”
      SaveFile(url, destination)

      Dts.TaskResult = ScriptResults.Success

      End Sub

      • sandeep says:

        Hi…I thank you for your SSIS package. It works fine if i work with a report withe one parameter. I am new to .net coding. I am using a table to populate report name and parameter and looping the script for each report name. But the export did not work for a report with two parameters. For example, i have a report with fromdate and todate i cannot make it to work. Is there any way to pass multiple different parameters and append to the query string ssrs url. I am guessing an array would work but i do not know as to how i can input array. Your help on this very much appreciated. Thank you again for the code.

      • Zoran Ivić says:

        Hi,
        I hope you managed to sent 2 parameter or you can make it simple by creating only one (just concatenate 2 things in one – for example if you have parameter 1 CODE and parameter 2 name then make 1 as codename (code 123 name Peter then you can have one filed in database 123Peter and you dont have to change package).

  2. Basem Saleem says:

    Hi,
    I have problem with big data files
    please if you can help me

    Thank you

  3. raviraja says:

    Hey Hi
    how to pass two parameters in a vb script..
    can any one help us..!!

    Thanx in advance!!
    regards,
    Ravi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s