在 ssis 包中呈现 ssrs 报表，并根据参数以 pdf 或 excel 或图像格式保存到文件夹

Question

我创建了SSIS包来呈现SSRS报告，并将报告以excel格式保存在特定文件夹中，例如"d：\test\report_ddmmyyy.xls">

以下VB脚本用于SSIS包脚本任务，以执行和SSSR报告并保存到Excel格式的文件夹

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

但是现在想使用 ssis 包在参数format_type ie pdf or excel,jpeg等上创建动态基础。

Answer 1

您需要在 URL 字符串中有条件地设置呈现格式。

.PDF

图像

EXCELOPENXML