Another way to access variables in SSIS 2008 Script Component
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 the Row 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 or Dts.VariableDispenser.LockForReadWrite, but you cannot access the VariableDispenser from within a Script 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):




Monday, February 15, 2010 at 2:56PM
Reader Comments