Need to run report for dates

See more:

SSIS

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_WPP_" + 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