Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Christchurch, New Zealand
    Posts
    3

    Unhappy Unanswered: 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 doesn’t 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 doesn’t 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").Va lue)
    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 Function
    Last edited by ChrisRose; 04-22-04 at 20:57.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie, this is what I would do....
    write 2 sub routines.......
    Code:
    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??

  3. #3
    Join Date
    Apr 2004
    Location
    Christchurch, New Zealand
    Posts
    3
    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

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  5. #5
    Join Date
    Apr 2004
    Location
    Christchurch, New Zealand
    Posts
    3
    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

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •