INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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...
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:

CODE

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("DownloadFolder").Value.ToString(), Dts.Connections("myProductFile").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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close