SSIS Write Variable Script Task

Question

Not sure what I'm doing wrong here - I am attempting to generate a variable which i will then pass to an FTP task.

In the Script Task, I set variable ReadWrite = User::WildCard. Currently, the User::WildCard variable has a String value of "test.csv". This script should rename the string value to "/forecasts/forecast_20120730.csv" for today. As of now, the script runs successfully, however the variable is not written.

#Region "Imports"
Imports System
Imports System.Data
Imports System.Collections
Imports System.Math    
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
#End Region

'ScriptMain is the entry point class of the script.  Do not change the name, attributes,
'or parent of this class.
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    'This method is called when this script task executes in the control flow.
    'Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    'To open Help, press F1.

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()

        Dim FilePart As String

        FilePart = Format(Now, "yyyyMMdd")

        Dts.Variables("WildCard").Value = "/forecasts/forecast_" & FilePart & "*.csv"
        Dts.TaskResult = ScriptResults.Success

    End Sub

End Class

rvphx rvphx · Accepted Answer · 2012-07-31T15:19:55

In your code, I dont see a LockForWrite anywhere. I think you need that in order to write back to a variable. Here is an excellent and simple example from Jaime Thomson's blog on how to do it. Hope this helps.

SSIS Write Variable Script Task

1 Answers