Using Resultset From SQL Task In Script Task

I have a SQL 2000 DTS which I imported in SSIS. The data transformation tasks were not converted, but saved as SQL 2000 transformation tasks. I want to re-write this, so that it is in compliance with 2005 standards. My problem is, I can't figure out how to capture data from a SQL task, and refer to its result set in a script task. Specifically, I want to capture data by querying a view, then refer to 2 fields in that result set in a script. I want to then transform a code into a full description, and write the transformed data to an Excel destination. The vbScript code that I used in 2000 is as follows:


'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
dim vEssenCode, vEssenDesc
vEssenCode = DTSSource("Essential_Code")
If vEssenCode = "E" Then
vEssenDesc = "EOC - Able to Perform"
Elseif vEssenCode = "D" Then
vEssenDesc = "Dept - Able To Perform"
ElseIf vEssenCode = "1" Then
vEssenDesc = "EOC - Not Able To Perform"
ElseIf vEssenCode = "2" Then
vEssenDesc = "Dept - Not Able To Perform"
Else vEssenDesc = "Non-Applicable"
End If
DTSDestination("Essen_Code") = vEssenDesc
Main = DTSTransformStat_OK
End Function


This worked fine until recently, but now always returns "Non-Applicable". Does anyone have any suggestions as to how I can accomplish this? I think that I have to create a data flow task with an OleDB source, send its output to an SQL Task, then send its result set to the script, do the transformation, and, finally, send its output to an Excel destination. Any suggestions will be greatly appreciated!!