How to set the value of a variable in SSIS using a script task

• jrgomont

  Old Hand

  Points: 313

  More actions

  May 15, 2013 at 2:41 pm

  #275248

  Hi,

  I have created a script task that takes User defined variables named:

  - As ReadOnlyVariables

  User::Counter,

  User::FileDate,

  User::FileExtension,

  User::IncomingCode

  - As ReadWriteVariables

  User::FileName

  Here is my script task code -- Using VB

  --------------------------------------------------------------------------------------------------------------------

  Public Sub Main()

  '

  Dim FileCounter As String

  Dim IncomingCode As String

  Dim FileDate As String

  Dim FileExtension As String

  Dim FileName As String

  FileCounter = CStr(Dts.Variables("User::Counter").Value)

  FeedCode = Dts.Variables("User::IncomingCode").Value

  FileDate = Dts.Variables("User::FileDate").Value

  FileExtension = Dts.Variables("User::FileExtension").Value

  FileName = FeedCode + "_" + FileDate + "_" + FileCounter

  ' The value is printing fine. I want to assign that value to the variable User::FileName in SSIS

  MsgBox(FileName)

  ' I tried doing this. But After I run the package the value does not get assigned. Am I missing something?

  Dts.Variables("User::FileName").Value = FileName

  Dts.TaskResult = ScriptResults.Success

  End Sub

  --------------------------------------------------------------------------------------------------------------------

  Help will be appreciated. Thanks

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  May 15, 2013 at 3:02 pm

  #1615572

  I use C# as my script language. This is from SQL 2008

  But in C# the DTS variables use brackets not parens.

  Dts.Variables["FileName"].Value = FileName.ToString();

  Place some of the other variables into your message box and see if they are shown.

  Andrew SQLDBA