posted 2/15/2010 by sqlrunner - Views: [4324]
I needed a way to access variables from within a Data Flow Task. The reason for this is counts. As I process records I need to count how many valid, invalid, already exist, etc. Normally the Row Count component would take care of this, but the process works on a single record at a time (using the thread framework I built), so if I need to work on a total of 100 records, the variable contained in theRow Count component will only have a 1 at any point (I call the Data Flow once per record). To solve I create a script destination component with the sole purpose of taking the count from within the Data Flow Task and adding it to a "global" variable, which I can then send in an email.Here is the Script Component(Destination):
I needed a way to access variables from within a Data Flow Task. The reason for this is counts. As I process records I need to count how many valid, invalid, already exist, etc. Normally the Row Count component would take care of this, but the process works on a single record at a time (using the thread framework I built), so if I need to work on a total of 100 records, the variable contained in theRow Count component will only have a 1 at any point (I call the Data Flow once per record). To solve I create a script destination component with the sole purpose of taking the count from within the Data Flow Task and adding it to a "global" variable, which I can then send in an email.
Here is the Script Component(Destination):
Notice the high-lighted variables. Normally I use the Dts.VariableDispenser.LockForRead orDts.VariableDispenser.LockForReadWrite, but you cannot access the VariableDispenser from within aScript Component. You need to actually use the ReadOnly and ReadWrite variables property. You make sure your "global" variables are ReadWrite and your "local" variables are ReadOnly. You access them via the Variables object. I have my sample listed below:
Within my Data Flow Task here is how I use the Script Component (destination):