May 27, 2015 at 7:21 am
I have a VB script which generate reports through SSIS. Below is the script which is running fine. Now I need to implement that report which should run for whatever date we want. I have a table called Date where we are specifing date for which we want to genrate. This date table has 1 row only where we specify date, So this package should pick the date from there. If no date specified in that Date table, It should generate data for today's date. How to implement this into my package specially in my script task.
I have specified a variables called:
Trans_Date which have hard coded value '05/27/2015'.
so below script running report for date 05/27/2015. I need to set it dynamically so it will run for daily. So what changes I have to make in package and script?
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ComponentModel
Imports System.Diagnostics
<microsoft.sqlserver.dts.tasks.scripttask.ssisscripttaskentrypointattribute()> _
<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("Folder_Destination").Value.ToString + "\" + "Daily_Report_" + Format(Now, "yyyyMMdd") + ".xls"
url = "http://ReportServer?/Reporting/SO_Invoicing&rs:Command=Render&rpt_date=" + Dts.Variables("Trans_Date").Value.ToString + "&rs:Format=EXCEL"
SaveFile(url, destination)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
Thanks in advance
May 27, 2015 at 9:30 am
Why even parameterize the date if you have the date needed in the table? Just change the query that exists for the report itself to do an INNER JOIN within the query to the table based on the date in the other data that would otherwise have to match the parameter. You can use a previous step in SSIS to validate that there is a record in the date table, and if not, insert today's date, then run the report step.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply