SSIS 2008 using the same variable for a row count and a script component

I have a row count component that uses a global variable X to store the number; up to this point it rules but then if I add a script component that has X 开发者_JAVA百科among its ReadOnly variables and then use it inside it for some purpose, the flow hangs on for a moment then it crashes saying row count needs a valid variable.

Isn't it possible to store the row count in a variable and then read this variable from a script comp. ?

You can not use a variable you populate in a Row Count transformation anywhere else in that same data flow. In the PreExecute phase of the data flow, the Row Count locks the variable. Attempting to use the variable in a Script component would have the Script try the same thing (at the same time) and SSIS therefore throws an error.

But even if that didn't happen, here's what else is going on. As your data flow works, and rows pass into the Row Count, it isn't actually filling the SSIS variable with the row count. That would be very inefficient, since the Row Count is actually a COM component and the SSIS variables are .Net. It's "expensive" to use .Net resources from COM and vice-versa. So the Row Count accumulates the value in a local code variable. In the PostExecute phase, the Row Count then pushes the value it collected into the SSIS variable. The key point is that the PostExecute phase happens after the data flow is complete. If you were able to view the variable at any time during the middle of the data flow (there are ways) you would see it valued at zero (or whatever value it was set to before the data flow started).

If you want to use a count of rows to perform a conditional action, or update rows within the flow, you have a couple choices:

• You can split the data flow into two data flows. Use a RAW file or SQL table to persist the "in-progress" data at the end of flow #1 so you can use it at the start of flow #2.
• You can use a Multicast to duplicate the flow, an Aggregate component to count it, and then do a cross-join to put the row count onto each row. (See Performing a Cross-Join (Cartesian Product) in SSIS.)

No, I don't think it is possible to use the variable used in Row Count to be used in Script Component as well.

My understanding that the variable is locked for write in the Row Count transformation and isn't available in Script Component transformation.

So, what are you trying to do with the variable inside Script Component? If you can specify your requirement, may be someone can suggest an alternative.

The article in this link suggests an approach: http://consultingblogs.emc.com/jamiethomson/archive/2007/02/28/SSIS-Nugget_3A00_-Output-the-rowcount.aspx

We can use the same variable 'Rowcount' to use in the script task as well ... only when the scope of that variable is at package level.[Global variable].

Most of the time I have used Script in the control flow and row count in the data flow. 1) If you want the count first make the data flow task(which will do the row count activity) in the control flow. 2) then drag the script beneath it and use the rowcount.

here is the example of the same. http://www.c-sharpcorner.com/uploadfile/muralidharan.d/how-to-use-rowcount-in-ssis/