assign an output of derive column to variable

I have a derive column( sequence) transformation in data flow , i am trying to assign this column to a variable , so that i can use it in the SQLtask control flow... how can i do this? can you show me some examples?

Why are you trying to assign a value from a derived column to a variable? The derived column works for every row passing through it. When passing a variable value back to the control flow, it would only contain the last value in the data flow.

With that said, you can use a script component's PostExecute sub to assign data to a variable.|||

Actually the derived column is in For each loop ( row by row process)...

"script component's PostExecute sub to assign data to a variable"

--> is there a code example that you can show me ?

Thank you for your help

|||

safddddddddddddddddddddd wrote:

Actually the derived column is in For each loop ( row by row process)...

No it isn't. You might have a data flow in the foreach loop.

The foreach loop can assign variables for each iteration. Why doesn't that work for you?|||

you are correct I meant I have a data flow which has the derive column transformation in the foreach loop..

I am tying to tryting to pass the result of derive column ( @.[User::Assessment_Dt] > assessment_dt ? 1 : 0 ) to a variable so that i can use it in SQLTASK ..

I understand i need to use a script task to assign the output of derive column in data flow to a variable ..but i don't know how? is there any examples that i can see?

|||What is the source for the foreach loop?

What is the source in the data flow?

I don't understand what you are trying to achieve, and I'd really like to help you architect this better.|||

The source of the foreach loop is

Select columns from S_enrollment_assessment

and i am not quite sure how to create the data source for the data flow in the for each loop since it it assign to row by row ?

|||

safddddddddddddddddddddd wrote:

The source of the foreach loop is

Select columns from S_enrollment_assessment

and i am not quite sure how to create the data source for the data flow in the for each loop since it it assign to row by row ?

So you're populating a data set with an execute SQL task and then using a foreach loop against that dataset?|||

yes.. and i assigned each columns to variables ( 71 of them) and follow the complex loding process..

for example,

FINDSTRING("1,2,3,4",@.TypeofAssessment,1) > 0 in precedence editor - expression

then go the SQLTASK1 otherwise go to SQLTASK2

and there are so many different logics that i need to follow...

so how to use 71 variables as the source of data flow in for each loop?

|||

safddddddddddddddddddddd wrote:

yes.. and i assigned each columns to variables ( 71 of them) and follow the complex loding process..

for example,

FINDSTRING("1,2,3,4",@.TypeofAssessment,1) > 0 in precedence editor - expression

then go the SQLTASK1 otherwise go to SQLTASK2

and there are so many different logics that i need to follow...

so how to use 71 variables as the source of data flow in for each loop?

You don't. You can write a SQL command in a variable expression that maps other variables (from your foreach loop) and then use the SQL variable as your OLE DB Source.

For instance:
@.TypeofAssessment - Source: Foreach loop
@.SQLCommand - Source: Expression: "select * from table where assessment = " + @.TypeofAssessment

OLE DB Source - Set to use sql command from variable - choose @.SQLCommand.|||yes. that is what i was trying to do...but since there are 71 columns that i am passing as variables,it's a kind of time consuming job.. thank you for all your help.. i really appreciate it..