DTS - Looping in a Data DrivenQuery

Hi Guys

I am reading a table one record at a time. Within this record a field can contain multiple values.
The delimiter is a ^. The data comes from a Pick legacy system
The data looks like this :-
chargeable_item_cd quantity text_1
I77C1^I77C2 1^1 PLATES /SCREWS^1.3MM SET

I want to extract the multiple values from this field and insert a record for each set of values.
I can unravel the data easy enough. The problem I have is how to loop within a DTS Activex
Script to store each of the values extracted from the field before moving onto the next record.
Is it possible or am I better of using a SQL task an taking a fraction of the time. (I am resisting
this as my boss doesnt like SQL code)
I am doing this within a data driven query.

Thanks in advance.

Cheers
Chris

The code I have so far looks like this(but doesnt work). It gives a "No query specification returnedby transform status".
'************************************************* **********
' Visual Basic Transformation Script
'************************************************* **********

' Copy each source column to the destination column
Function Main()
DTSDestination("DHB_Key") = DTSLookups("DHB Lookup").Execute(DTSGlobalVariables("DHB_Code").Value)
DTSDestination("Health_Encounter_Theatre_Key") = DTSSource("rule_violtd_cd")

DTSDestination("Patient_Key") = DTSLookups("Patient Lookup").Execute(left(DTSSource("admit_id"), 7))

DTSDestination("Patient_Care_Episode_Key") = _
DTSLookups("Patient Care Lookup").Execute( DTSDestination("Patient_Key"), _
"*T" + DTSSource("hosp_cd") + DTSSource("scout_sheet_nbr"), _
DTSDestination("DHB_Key"))

DTSDestination("Health_Encounter_Key") = DTSLookups("Hlth Encntr Lookup").Execute( DTSDestination("DHB_Key"), _
DTSDestination("Patient_Key"), _
DTSDestination("Patient_Care_Episode_Key"), _
"TH")
' This piece of code does it for multi field values for charagble items to individual values that can be stored in the database

' Copy each source column to the destination column
DIM string1, string2, string3, sitem, sqty, sdescript, quantity, descript
string1 = DTSSource("chargeable_item_cd")
string2 = DTSSource("quantity")

Do While InStr( string1 , "^") > 0
sitem = left(string1, InStr( string1 , "^") -1 )
sqty = left(string2, InStr( string2 , "^") -1 )
sdescript = left(string3, InStr( string3 , "^") -1 )

DTSDestination("Chargeable_Items_Key") = DTSLookups("Chargeable Items Lookup").Execute(sitem)
DTSDestination("Quantity") = sqty
DTSDestination("Description_Of_Item") = sdescript

If IsNull(sqty) Then
quantity = 0
Else
quantity = sqty
End If

If IsNull(sdescript) Then
descript = "X"
Else
descript = sdescript
End If

If NOT IsNull(DTSDestination("Chargeable_Items_Key")) Then
If DTSLookups("Item Exists Lookup").Execute(DTSDestination("DHB_Key"),_
DTSDestination("Health_Encounter_Theatre_Key"),_
DTSDestination("Patient_Key"),_
DTSDestination("Patient_Care_Episode_Key"),_
DTSDestination("Health_Encounter_Key"),_
DTSDestination("Chargeable_Items_Key"),_
quantity,_
descript) = 0 Then
Main = DTSTransformstat_InsertQuery
Else
Main = DTSTransformStat_SkipRow
End If
Main = DTSTransformStat_OK
End If

string1 = Mid( string1 , InStr( string1 , "^") + 1, Len( string1 ) )
string2 = Mid( string2 , InStr( string2 , "^") + 1, Len( string2 ) )
string3 = Mid( string3 , InStr( string3 , "^") + 1, Len( string3 ) )
loop

Main = DTSTransformStat_OK
End Functionokie, this is what I would do...
write 2 sub routines......

Sub InsertItems(strItems)
Dim arrItems, iItem
arrItems = split(strItems,"^")
for iItem = 0 to uBound(arrItems)
ExecuteSQL("insert into itemTable (column) values (" & arrItems(iItem) & ")"
next
End Sub

Sub ExecuteSQL(strSQL)
On Error Resume Next
Dim oConn, oRs, strConn
Set oConn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")

strConn = "Your connection string goes here"
oConn.ConnectionString = strConn
oConn.ConnectionTimeout = 30
oConn.Open
oConn.execute(stqSQL
set oConn = nothing
On Error Goto 0
End Sub

in your main function call InsertItems and pass it the value of chargeable_item_cd (assuming I got the name right)

Make sense??|||Hi Rokslide

I take it that this will work inside the Activex Script Transformation Properties portion of a data driven query.

It is nearly knock off time Friday afternoon in NZ so I will try it on Monday morning
.
Cheers
Chris|||Hi Chris,

Your Activex Script can have as many extra functions or sub routines as you like with no problems providing there is only 1 main function.

Nearly knowing off time?? It's only 2pm! ;) It is Friday though and the lead in to a long weekend so... ;)

Where are you working in Christchurch? I used to live and work there myself. :)|||Hi

I am based at Princess Margret Hospital by under the Port Hills but
I live way out in the country about 10 kms the other side of Rangiora
on a 52 acre farmlet.

Cheers|||Cool. :) I used to work for the Council there and then started a company with a friend. Before living in Christchurch I was living out in Cheviot.

Mmmmm country air... ;)

Let me know if you need any more help with that DTS package.