SSIS: Assign a value to Variable using Dataflow Script task

Who is online?  0 guests and 0 members
Home  »  Blogs  »  PatrickLeBlanc  »  SSIS: Assign a value to Variable using Dataflow Script task
 
0
/5
Avg: 0/5: (0 votes)

Comments (3)

PatrickLeBlanc

Thanks toddmcdermid! I attempted the approach, but it did not work on the Data Flow.  I will read the article and hopefully it will shed some light on my challenges.  Thanks again.

9/20/2010
 · 
 
by
user809288
user809288 said:
Hi Patrick, I think Todd has a good point, and his article cleared up some confusion for me. http://toddmcdermid.blogspot.com/2010/08/use-readonlyvariables-and.html What you're doing isn't necessarily wrong, but it is more complicated than it needs to be. Using your same variables, this is the comparable code that should work: public class ScriptMain : UserComponent { int iScriptVar; public override void PostExecute() { base.PostExecute(); Variables.intMaxSalesDetailsID = iScriptVar; } public override void Input0_ProcessInputRow(Input0Buffer Row) { if (!Row.SalesDetailID_IsNull) iScriptVar = Row.SalesDetailID; } } It's doing a similar process, but the locking and unlocking is all handled behind the scenes, so the code becomes more straightforward and maintainable. The crux here is that you need define a script variable with the scope of the whole dataflow task, assign that variable inside the ProcessInputRow section, and then set the package variable in the PostExecute section. As Todd points out, the only place you can set package variables is in the PostExecute section. As an implicit consequence of this, you won't be able to set package variables row-by-row inside the dataflow. However, this seems reasonable as inside the dataflow you should be able to access and manipulate the data using normal transforms like Derived Column. Package variables are needed to deliver information from inside the dataflow back to the control flow, which is why it makes sense that you should only be able to set them once on PostExecute. (Sorry to bump an old topic, but I had trouble locating reliable info on this, and I think this clarification might be helpful.) Jenn
6/25/2012
 · 
 
by
user809288
user809288 said:
Ack! Sorry about the formatting on that last comment; it removed all my formatting. Strangely, I always get that on this site. Is it because I'm logged in through my google account? Or because I'm using Chrome? Or something else?
6/25/2012
 · 
 
by
Blogs RSS Feed

PatrickLeBlanc's latest blog posts

Blogs RSS Feed

Latest community blog posts