Script component and variable!!

Hi All,

I have developed a simple package utilizing script component and variable. What I am trying to do is declare a variable in the parent package that can be change at run time to either 1 or 0. If 1 I want the package to succeed and branch off to run some child packages and if 0, then branch the other way and run another child package. In ether way, I want to be able to load one of the two child packages and not both.

I I declare a variable in my parent package like IsExist, int32 with default 0.

Then I have this code thanks partly to Jamie Thomson in " SSIS: Writing to a variable from a script task"

Public Sub Main()

'

' Add your code here

'

Dim t As Integer

Dim vars As Variables

Dts.VariableDispenser.LockOneForWrite("IsExists", vars)

t = CInt((Dts.Variables("IsExists").Value))

'Dts.Variables(IsExists).Value = t

If t >= 1 Then

vars.Unlock()

Dts.TaskResult = Dts.Results.Success

Else

Dts.TaskResult = Dts.Results.Failure

End If

End Sub

End Class

I also place the variable name in the ReadWriteVariables in the script component.

The problem now is it is always failing. It stopped at the script task and fail. This is not what I want. I want the package to execute another task upon failure. Anyone knows what I am doing wrong?

Hi,

You can achieve this without using a script task. Simply use expressions on your precedence constraints to check the value of IsExists. Here's how: http://www.sqlis.com/default.aspx?306

Oh, and you may want to make IsExists a boolean variable as well.

-Jamie

|||

Thanks alot Jamie. I tried using the Precedence constraint by seting the @.IsExists to 1 on one flow and 0 on the other but it is always running the one for 0. This is without using script task.

Again, when I use a script task with the following code, it gave me bunch of errors:

Public Sub Main()

Dim vs As Variables

'We need to lock the variables so we can read it without anything else changing it
Dts.VariableDispenser.LockOneForWrite("IsExists", vs)

'Assign it a value
vs.Item("IsExists").Value = (Dts.Variables("IsExists").Value)

'remember to unlock the variable now
vs.Unlock()

Dts.TaskResult = Dts.Results.Success
End Sub

Thanks

Omon

|||

OK, so you're using the script task to set the variable as well. I see.

What errors are you getting?

|||

Thanks jamie,

Yes. I used below script to set the value:

Dim vs As Variables

Dim t As Boolean

'MsgBox("variable_Passed)

t = CBool((Dts.Variables("IsExists").Value))

If t = True Then

'MsgBox("Condition_Stage")

Dts.TaskResult = Dts.Results.Success

'MsgBox("Success")

Else

Dts.TaskResult = Dts.Results.Failure

'MsgBox("Failure")

End If

So I was getting conversion error and when I placed CBool in front of the IsExists like: CBool((Dts.Variables("IsExists").Value)) it worked. I had to set

LockOneForWrite in the script task instead of using the code.

Again, I also had to select Logical OR in the presedent editor . This fix my problem.

I think I am all good for now.

Thanks very much.

Omon