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..
