How can I get value from data flow control?

How can I get value from data flow control when it returns to the Control flow?

I want to use values in the data flow to decide what I'm doing next - using an expression on the 'line' (i.e. @.Step == 10, 20 , 30 ...)
@.Step - is user defined variable.

How can I set @.Step according to the data flow ?

It seems to be that I can't change @.Step in the data flow.
Its not clear what you mean by set @.step according to the data flow. According to what? In any event most components do no set variable values in the dataflow they just look at them. The exceptions are row count and script (there could be another but I don't recall it off the top of my head). Row count only sets a variable at post execute. The script component can set a variable whenever it wants but in order to do so it can not use the variables defined on the ReadWrite variables line as those can only be set during post execute. In order to set a variable during execution you need to use the variable dispenser to lock it and then you can set it to whatever you would like. Remember to unlock it or else anything else trying to lock it will fail.

Thanks,
Matt|||

The reason I need this is because I want to define a table of tasks and that the process will continue according to the next step on the table (not as a fix path).

I don't see a script control in the data flow - so how can I change the variable ?

Thanks,

Yossi.

|||

Where is your problem?

1. Define your variable (In this example it is named Vari )

2. Drag a Script-Component (transformation) to your DataFlow-Task.

3. Select theScript Tab

4. Enter the name of the variable (Vari ) as the value of the ReadWriteVariables.

5. Click Design Script.

7. Edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

End Sub
Public Overrides Sub PostExecute()
Me.Variables.Vari = 1234
End Sub

End Class

Notice, there is no code in the ProcessInputRow. You can delete it.

|||

Thanks, it is helpful.

I want to change the variable according to a data source.

How can I access the data source which was entering to the Script-Component within the script?

.

|||

Check the columns you need in the Input Columns.

and then edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim Max As Integer

Public Overrides Sub PreExecute()
Max = 0
End Sub

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
If Row.InputColumn > Max Then Max = CInt(Row.InputColumn)
End Sub

Public Overrides Sub PostExecute()
Me.Variables.Vari = Max
End Sub
End Class

|||

I'm geting this msg:

"sub 'Input_ProcessInputRow' can not be declared 'override' because it dose not override a sub in base class"

|||

Seems like you are editing Script Task instead of Data Flow Script Transform (or maybe Data Flow Script Component, but Source instead of Transform).

Of course, it is possible the script task (or existing task, e.g. Execute SQL Task) is what you really need - depending on your needs and what you mean by "change the variable according to a data source". Could you describe it in more detail?

I also suggest reading SQL Books Online to get general understanding of SSIS architecture, the difference between tasks and transforms, etc.