Can't set a package variable from local variable in Data Flow script component.

Question

I was reading this but did not find an aswer to my problem:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/feeac892-1fc2-442e-bbd9-73ebe5529e79?prof=required

At least, the aswer given is not working. I have a package variable called InsertType of type string. I want to set it to a value of a local variable inside Data Flow (NOT a Control Flow) in script task. I can set the local variable with no issues. I use a message box for that. When I come to PostExecute() to set my package variable from local variable, nothing is happening. I mean, the package variable remains the same. I added an If .... Else just to make sure I come to a correct place based on the value of a local variable. But instead of the whole If...Else can be just:

Me.Variables.InsertType = sInsertType

  

Public Class ScriptMain

    Inherits UserComponent

    Dim sInsertType As String

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        If Row.RowExists_IsNull Then

            sInsertType = "Insert"

            'MsgBox("Local variable now is " & sInsertType.ToString)

        Else

            sInsertType = "Update"

            'MsgBox("Local variable now is " & sInsertType.ToString)

        End If

    End Sub

    Public Overrides Sub PostExecute()

        MsgBox("In PostExecute sub local variable is " & sInsertType)

        If sInsertType = "Insert" Then

            Me.Variables.InsertType = "Insert"

        Else

            sInsertType = "Update"

            Me.Variables.InsertType = "Update"

        End If

        MsgBox("PostExecute Package variable now is " & Me.Variables.InsertType)

        MyBase.PostExecute()

    End Sub

End Class

---

Stan

Answer 1

How do you know the package variable remains the same?  How are you checking that outcome?

---

Talk to me now on

Answer 2

Stan,

The Package variable you have assigned cannot be used in the same data flow. Whereas, you can use it after your DFT. If you need to access this value within your DFT... then use derived column instead.

---

Thanks Prasoon

Answer 3

I just did what you have done above (with the if else removed from the post execute block) and package variable does get set. To verify that i plonked in a script task after my data flow and it showed the updated value. Can you also verify the value of your variable after the data flow task has executed using a script task.

---

http://btsbee.wordpress.com/

Answer 4

Todd,

Message box MsgBox("PostExecute Package variable now is " & Me.Variables.InsertType)

and then after the DFT I have two outcomes based on the values of InsertType - preference constranits i.e.

I check @InsertType=="Insert" and @InsertType=="Update"

Although the local variable is set to Update in my testing, and i want to pass it to package variable the "Insert" outcome fires.

Stan 

---

Stan

Answer 5

Prasoon, please see my previous reply. I use that variable after the DFT completes.

Stan

---

Stan

Answer 6

Please, see my reply to Todd. I use my variable after the DFT with two outputs based on the package variable. Do I have to do any more settings? I put my InsertType variable in ReadWriteVariables in Custom Settings on the script task to be able to write to it.

Stan

---

Stan

Answer 7

Todd, I forgot to mention I set the InsertType (package level variable) as an Expression to be "Insert" to just initialize it. I thought the script will override the initial value. I change the EvaluateAsExpression to False and removed the "Insert" (I can't completely remove the quites, so I left it as ""). Now, when I run my script, I see the following for this code:

Me.Variables.InsertType = sInsertType

or

Me.Variables.InsertType = CStr(sInsertType)

The type of the value being assigned to variable "User::InsertType" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

The sInsertType is a string and the InsertType is a string as well.

If I do this, it works:

Me.Variables.InsertType = "whatever"

What am I doing wrong?

---

Stan

Answer 8

What do you mean "I can't completely remove the quites"?

Just delete them, put in an initial textual value.

Is your variable declaration is string?

---

Arthur My Blog

Answer 9

This is what strange. As I said, I set EvaluateAsExpression to False but I can't completely remove the content of the Expression box. So, I just leave the quotes and the values initially sets to blank. Yes, my package variable is a String.

---

Stan

Answer 10

Right-click on the designer canvas and click on "Variables..." context menu item once popped up modify the variable content in there

---

Arthur My Blog

Answer 11

I figures out my issue. Well, it was a kind of crazy...... In my code inside the data flow I send new rows into an OLE DB Destination and for any updates I send them to the OLE DB Commnd to execute an Update in SQL. Well, I put the script aftet the OLE DB (the Update path) as a "destination" hoping to set the variable there. But imagine that for an Insert the data goes different path and althoug the script fires anyway, the Package level variable doesn't get set properly. I am still puzzled why the local variable got set and not the package level.

Anyway, I placed the same script before I slpit my output as a "transformation" and everything worked!!! In this case, the local variable always gets set and hence the package variable.

Thank you all for your attempts to help me!

---

Stan

Answer 12

The variable scope I suspect was the issue, and you used the same name for both

---

Arthur My Blog

Answer 13

You also should not have been using EvaluateAsExpression - when that's true, it wouldn't have mattered what you set the variable's value to.

If you need to set a "default" value for the variable, just type a value into the "Value" in the variable's properties.

---

Talk to me now on