I can see how to assign properties from variables, but not the other way round.
Can anyone explain to me how to do this?
Many thanks
RudyHi,
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 L?wen
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
|||AndyCan you explain me what your script task exactly does
I'm searching for a script that changes the sql-commands
Thx
