Problems with a DTS ActiveX Script

[nakkii]

I am importing address data from an older database into SQL 2000. The address is in the form of "Denver, CO 80014", I am trying to separate the address into three columns. My first step is to split the string at the "," and take the first part and make it the City. Then take the second element of the split string and take the last part as the Zip (I did this because the ZIP could be 80014-2112) The remaining part of the string is then the State. I am recieving the following error:

**********************************************************
Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Subscript out of range: '[number: 0]'

Error on Line 208
**********************************************************

The subscript it is refering to is the "asElements" index number, I have tried changing this and it only changes the '[number: 0]'

I have run this script in VB and it works like it is suppose to, but when I modify it for DTS it fails. What am I doing wrong.

Here is the DTS script:

'**********************************************************
' Address Break Down
'**********************************************************

Dim asElements
Dim sCity
Dim sState
Dim sZIP
Dim strTmp
Dim intI
Dim intP

' Split the string up at the 'comma'
asElements = Split(TRIM(DTSSource("CompTrustCitySt&quot), ",&quot

' If there aren't two elements discard all information
If UBound(asElements) < 1 Then
DTSDestination(&quot;CompTrustCity&quot = Null
DTSDestination(&quot;CompTrustState&quot = Null
DTSDestination(&quot;CompTrustZip&quot = Null
End If

' Assign the first element (city) in the array
DTSDestination(&quot;CompTrustCity&quot = TRIM(asElements(0))

' Assign the second element to a temp string
strTmp = TRIM(asElements(1))
intP = 1

' Set the length of the last part of the string to the space
For intI = Len(strTmp) To 1 Step -1
If Mid(strTmp, intI, 1) = &quot; &quot; Then
intP = intI + 1
Exit For
End If
Next

sZIP = Mid(strTmp, intP)
sState = UCase(Trim(Left(strTmp, intP - 1)))

' Validate Data
If Len(sZIP) = 2 And Len(sState) = 0 Then
sState = sZIP
sZIP = &quot;&quot;
ElseIf Len(sState) > 2 Then
sState = &quot;&quot;
End If

If Len(sZIP) <> 10 Then
If Len(sZIP) <> 5 Then
sZIP = &quot;&quot;
End If
End If

If IsNumeric(Left(sZIP, 5)) = False Then
sZIP = &quot;&quot;
End If


DTSDestination(&quot;CompTrustState&quot = sState
DTSDestination(&quot;CompTrustZip&quot = sZIP

Main = DTSTransformStat_OK

End Function

 

[nakkii]

Never mind, I found the problem......


Persistence....Nothing in the world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent.