Using a dynamic flat file connection as the output from a VB Script Task

[Welshbird]

Hi All,

I'm not an expert at SSIS by any means but am trying to learn as I go along.

I've been following the instructions from this web page

http://microsoft-ssis.blogspot.de/2011/05/download-source-file-from-website-with.html

And have got most of the way to do what I need.

However, for me the file name I'll be saving is different every month. I have some SQL that 'calculates' the file name and indeed the URL to collect this from. Both of these are stored in variables.
In debug mode I can see that both variables are calcuated correctly.

The URL variable is populated fine within the VB Code whereas the other variable i'm using as an expression within a flat file connection to dynamically change as the file name changes - but when I debug the VB code that connection string is empty.

VB Code as follows:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net ' Added
<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
    Public Sub Main()
        Try
            ' Logging start of download
            Dim fireAgain As Boolean = True
            Dts.Events.FireInformation(0, "Download File", "Start downloading " + Dts.Variables("DownloadFolder").Value.ToString(), String.Empty, 0, fireAgain)
            ' Create a webclient to download a file 
            Dim mySSISWebClient As WebClient = New WebClient()
            ' Download file and use the Flat File Connectionstring (Dynamically build by SQL) 
            ' to save the file (and replace the existing file)
            mySSISWebClient.DownloadFile(Dts.Variables("[COLOR=#E9B96E]DownloadFolder[/color]").Value.ToString(), Dts.Connections("[COLOR=#FCE94F]myProductFile[/color]").ConnectionString)
            ' Logging end of download 
            Dts.Events.FireInformation(0, "Download File", "Finished downloading " + Dts.Connections("myProductFile").ConnectionString, String.Empty, 0, fireAgain)
            ' Quit Script Task succesful
            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception
            ' Logging why download failed
            Dts.Events.FireError(0, "Download File", "Download failed: " + ex.Message, String.Empty, 0)
            ' Quit Script Task unsuccesful
            Dts.TaskResult = ScriptResults.Failure
        End Try
    End Sub
End Class

DownloadFolder is the variable that I can see within the VBcode, and myProductFile is empty.

Any ideas would be so gratefully received right now.

Thanks chaps.

Fee

"The cure for anything is salt water û sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

OK - So I'm a twit.

It needed to be a FILE connection not a FLATFILE connection as it is a self-extracting zip.

Still, good to have learnt this!

Fee

"The cure for anything is salt water û sweat, tears, or the sea." Isak Dinesen