Wednesday, March 7, 2012

how to store values during dataflow

The point is, i want to calculate the max id of a table using Aggregate Transformation, then insert some rows with a OLEDB Command and finally , with another OLEDB Command select those rows with id >(max_id) calculated before.

How can i get a value that was calculated before? Can i store it in a variable?

Many Thanks!

On the Control Flow add a 'Execute SQL Task' which has something like SELECT MAX(ID) AS MAXID FROM YOURTABLE, store the result into a variable.

Do your inserts, then SELECT * FROM YOURTABLE WHERE ID > YOURVARIABLE

|||

Thanks Paul,

The only way to store this results on a variable is from an 'Execute SQL Task'? can i do the same from either a OLEDB Command or Agregate Transformation?

|||You can use a script component to store values in a variable inside a data flow. You have to write it in the PostExecute method, though, which runs after all the rows pass thorugh it. However, I don't know if that is the best approach, given the scenario you are describing. But, ultimately, it's your call.|||

Thank you very much jwelch, ill try it!

No comments:

Post a Comment