Assigning properties to variables

Assigning properties to variables

• Neil Kirby

  I am doing exactly this with a script task. 
  
  When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.
  
  Here is a sample of the script which should give you and idea how to set the value of a package level variable.
  
  

  Public Sub Main()

  Dim VarName As String = ""

  Try

  Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

  VarName = "User::ObjectName"

  varCurrent = Dts.Variables.Item(VarName)
  

  ' Set the current value of the Variable
  varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

  VarName = "Completed"

  Catch Ex As Exception

  Dts.TaskResult = Dts.Results.Failure

  Dts.Events.FireError(1, "Validate Variables", String.Format("Missing one of the following variables [User::Phase, User::ObjectName, User::ObjectType, User::StepName]. These Variables must be defined. Current Variable ='{0}'", VarName),Nothing, 0)

  Return

  End Try

  ' Found all variables. Let the Phase run

  Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

  Dts.TaskResult = Dts.Results.Success
  
  
  

  
  

• MartinHills

  Hi,
  
  i've done something like this  with a Script Task in SSIS.
  
  1. Create some Variables you need in the Variables Window
  2. Create a Script Task and put you Readonly and Readwrite Varibales at the
      Properties of the Script Task Editor.
  3. Use the Design Scritp Button at this Task to define a Script.
  4. My Script was designed to create dynamic SQL Statements:
  
  

  Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

  Dim Table As String = CStr(Dts.Variables("TSDES").Value)

  Dim SB As New System.Text.StringBuilder(1024)

  'Create Table

  SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

  SB.Append("BEGIN" & vbNewLine)

  SB.Append(" BEGIN Transaction" & vbNewLine)

  SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

  SB.Append(" (" & vbNewLine)

  SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

  SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

  SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

  SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

  SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

  SB.Append(" ) ON [PRIMARY]" & vbNewLine)

  SB.Append(" COMMIT" & vbNewLine)

  SB.Append("End" & vbNewLine)

  SB.Append("ELSE" & vbNewLine)

  SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

  Dts.Variables("CREATETABLE").Value = SB.ToString

  SB.Remove(0, SB.Length)
  ...
  
  That's it.
  I hope this could be helpfull for you.
  
  Kind Regards
  Andy Lowen

  
  

• Susan Still

  Andy
  
  Can you explain me what your script task exactly does
  
  I'm searching for a script that changes the sql-commands
  
  
  Thx
  
• Assigning properties to variables