SSIS Variables and the Script Component

About 2 weeks ago, yeah I know, why wait 2 weeks to blog on something, I was working with a script component in SSIS and I thought I needed to use some Package or Dataflow level variables in order to accomplish the task before me. Key words in that last sentence, "I thought". They are key, not because it shows I actually thought about what I was doing, but because I had thought incorrectly. Not exactly a new thing for me either, but I digress. The task I was trying, and eventually was successful at, was taking a delimited string of ministries or job titles, and breaking them out into individual rows for each ministry while also creating a column on which to group. The format of the source data is something like this:

PersonNo Location StartDate EndDate  Ministries
-------- -------- --------- -------  ----------
12346       A     08/01/92  08/31/92 Teacher
12346       A     09/01/92  12/14/92 Teacher, Language Consultant
12346       A     12/15/92  12/31/95 Teacher, Language Consultant, Administrator
12346       A     01/01/96  05/31/96 Teacher, Language Consultant, Administrator, Bookkeeper
12346       A     06/01/96  01/06/08 Bookkeeeper

The desired output of the script component is:

PersonNo   Location  StartDate  EndDate    Ministry            Groups
---------- --------- ---------- ---------- ----------          -------
12346         A      08/01/92   08/31/92   Teacher               1
12346         A      09/01/92   12/14/92   Teacher               1
12346         A      09/01/92   12/14/92   Language Consultant   2
12346         A      12/15/92   12/31/95   Teacher               3
12346         A      12/15/92   12/31/95   Language Consultant   4
12346         A      12/15/92   12/31/95   Administrator         5
12346         A      01/01/96   05/31/96   Teacher               6
12346         A      01/01/96   05/31/96   Language Consultant   7
12346         A      01/01/96   05/31/96   Administrator         8
12346         A      01/01/96   05/31/96   Bookkeeper            9  
12346         A      06/01/96   01/06/08   Bookkeeper            9

The desired final output of the Dataflow is this:

PersonNo Location  StartDate EndDate  Ministry
-------- --------- --------- -------- ----------
12346       A      08/01/92  12/14/92 Teacher
12346       A      09/01/92  12/14/92 Language Consultant
12346       A      12/15/92  12/31/95 Teacher
12346       A      12/15/92  12/31/95 Language Consultant
12346       A      12/15/92  12/31/95 Administrator
12346       A      01/01/96  05/31/96 Teacher
12346       A      01/01/96  05/31/96 Language Consultant
12346       A      01/01/96  05/31/96 Administrator
12346       A      01/01/96  01/06/08 Bookkeeper

In order to get the Groups I needed to be able to compare the current row to the previous row. In a script component you only have access to the current row, so I assumed (yes, I know what that means), that I would need dataflow scoped variables to contain the pertinent values from the previous row. I created the needed variables and then entered them in the ReadWrite Variables property of the script component to simplify access to them. Well, then when I ran the dataflow it failed at the script component with the error:

The collection of variables locked for read and write access is not available outside of PostExecute

The issue with this is that PostExecute only runs after ALL the rows have been processed which did not meet my needs as I could not modify them when the values changed. So I googled the error message trying to find out how to get around the error. First, I decided to remove the variables from the ReadWrite property of the dataflow. Then I used the variable dispenser to access the variables. Hey, believe it or not this works! You can unlock the variables for read and write using the variable dispenser and make the modifications. I thought I had solved my problem. Then over the weekend I realized that since the PostExecute function only runs after ALL the rows have been processed, this means that the Script Component is created once within the dataflow. So instead of needing the variables to be scoped to the dataflow, I only needed class level variables within the script component. Lesson learned.

The only reason you would need variables scoped outside the the script component is if you need the data OUTSIDE the script component.

The great part about the whole process was that I learned how to use an asyncronous script component and more details about how the script component works.