qluo


Hi, I have a script task in SSIS which dynamically generates a string for a file name to be used as flat file source. I execute the task and it executed with success; but when I checked the result of the variable TotFileName from the Expression builder window for the flat file connection manager it was not populated with a file name like \\MyServer\MyDrive\MyFolder\200706daily.txt. So something might still be missing from the script below. Or is the way I do it correct Can someone help with this Thanks a lot!!

I have created package level variables ImportFolder (value like: \\MyServer\MyDrive\MyFolder ) and TotFileName (value field empty) and make ImportFolder a ReadOnlyVariable and TotFileName a ReadWriteVariable. Then I use expression to set the property for flat file connection manager to use the TotFileName variable.

(Basically the idea is: if now is July 2007 then the filename should be 200706daily.txt; if now is Jan 2008 then the filename should be 200712daily.txt)

-----code------

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim TotFileName As String
Dim TrueFileName As String
Dim sYear As String
Dim sMonth As String
Dim sDate As String

sYear = CStr(Year(Now()))
sMonth = CStr(Month(Now()) - 1)

If (Month(Now()) < 11 And Month(Now()) > 1) Then
sMonth = "0" & sMonth
End If

If (Month(Now()) = 1) Then
sMonth = "12"

sYear = CStr(Year(Now()) - 1)
End If


sDate = sYear & sMonth

TrueFileName = sDate & "daily.txt"

TotFileName = CStr(Dts.Variables.Item("ImportFolder").Value) & TrueFileName

Dts.Variables.Item("TotFileName").Value = TotFileName

Dts.TaskResult = Dts.Results.Success
End Sub

End Class




Re: Why was ReadWriteVariable not populated/updated with a value of the dynamically generated string?

Phil Brammer


Setting the value of a variable is a run-time thing. After the script runs, you won't have the last value generated by the script stored in the variable. The variable will revert back to the default set at design time.






Re: Why was ReadWriteVariable not populated/updated with a value of the dynamically generated string?

qluo

Then how should I approach this Script task doesn't work for my case What other way should I use

Thanks!






Re: Why was ReadWriteVariable not populated/updated with a value of the dynamically generated string?

Rafael Salas

Cannot you reproduce that same using an expression in the variables expression property






Re: Why was ReadWriteVariable not populated/updated with a value of the dynamically generated string?

Phil Brammer

qluo wrote:

Then how should I approach this Script task doesn't work for my case What other way should I use

Thanks!



You're not understanding what I wrote. It looks like you've got it setup okay. When you run the package, it should pick up the correct value. Just because it's not there at design time doesn't mean it's not working.





Re: Why was ReadWriteVariable not populated/updated with a value of the dynamically generated string?

qluo

Thanks! Since we just started using SSIS newly I was still thinking the DTS way where I can see the updated value of a global variable in the next step after just executing the previous step.