Я создал пакет SSIS для отображения отчета SSRS и сохраненного отчета в формате Excel в определенной папке, например "d:\test\report_ddmmyyy.xls"
следующий сценарий vb используется в задаче сценария пакета ssis для выполнения, отчета sssrs и сохранения в папку в формате 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
Но теперь нужно создать динамическую базу по параметру format_type
ie pdf or excel,jpeg
и т. д., используя пакет ssis.