CODEWITHSUNDEEP
sql-servervb.netreporting-servicesssisssis-2008
prasad
prasad

Reputation: 25

Render ssrs report in ssis package and save to folder in pdf or excel or image format base on parameter

I have create SSIS package to render SSRS report and saved report in excel format in particular folder eg "d:\test\report_ddmmyyy.xls"

following vb script is used in ssis package script task to execute and sssrs report and save to folder in excel formate

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<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(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("folderdestination").Value.ToString + "\" + "Report_" + Dts.Variables("ReportParameter").Value.ToString + "_" + Format(Now, "yyyyMMdd") + ".xls"'
        destination = Dts.Variables("folderdestination").Value.ToString + "\" + "Report_" + Format(Now, "yyyyMMdd") + ".xls"
        url = "http://sbr-sqldb-01/ReportServer/Pages/ReportViewer.aspx?%2fssrsreport%2fReport1&rs:Command=Render&rank=" + Dts.Variables("ReportParameter").Value.ToString + "&rs:Format=EXCEL"
        SaveFile(url, destination)
        Dts.TaskResult = ScriptResults.Success
        ' Dts.TaskResult = ScriptResults.Success'

    End Sub

End Class

But now want to create dynamical base on parameter format_type ie pdf or excel,jpeg etc using ssis package.

Upvotes: 1

Views: 2888

Answers (1)

Wes H
Wes H

Reputation: 4439

You need to conditionally set the render format in your URL string.

PDF

IMAGE

EXCELOPENXML

Upvotes: 1

Related Questions