Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Montevideo, Uruguay, South America
    Posts
    13

    Angry Unanswered: DTS!!!!! Plese help!

    I've been stuck with this for a week and I seriously need help!

    I have a DTS package with only 2 tasks, an ExecuteSqlTask that loads global variables and a TransformDataTask.

    The TransformDataTask "source" is a query over one table, and the "destination" is another table. The transformation itself is an ActivexScript, written in VBscript, that performs several checks over source data and retrieves "data warehouse keys" from the DW tables using lookups.

    So far, everything seems ok.
    If I test the transformation it works fine, and produces a text output that (for testing purposes) I have tried to insert into the destination table, and this also works!!!!
    But when I try to execute the package via Enterprise Manager the execution halts. I've set "Row Count Step" to 1, in order to guess wich line had the problem. This number changes but independetly from sorting data. Execution halts when less than 100 rows have been processed!!!! It actually doesn't halt, it says "Running" but stays like that for even days!!!!!!

    Ok, I' ll send the code.
    All the lookups "retrieve" values, except "actualiza_valores" that performs an update.

    Any ideas would be apreciated!!!!
    thanks in advance
    lorena

    Function Main()

    Dim ASIGNADA
    Dim CONFIRMADA
    Dim NO_REALIZADA
    Dim ULTERIOR
    Dim PRIMERA_VEZ
    Dim PROGRAMADA
    Dim NO_PROGRAMADA
    Dim NUEVA
    Dim REPETIDA
    Dim fecha
    Dim TIEMPO_ESPERA
    Dim cantAnio
    Dim edad
    Dim msg

    ASIGNADA=0
    CONFIRMADA=0
    NO_REALIZADA=0
    ULTERIOR = 0
    PRIMERA_VEZ=0
    NUEVA = 0
    REPETIDA=0
    TIEMPO_ESPERA= 0
    PROGRAMADA = 0
    NO_PROGRAMADA = 0
    cantAnio = 0
    msg="hola"

    'Hay que ver si hay una consulta para el mismo paciente, profesional, servicio y fecha.
    'Si es asi se actualizan las medidas de ese hecho.
    'El tiempo de espera sera el promedio de los tiempos de espera

    fecha = DTSLookups("get_id_fecha").Execute(DTSSource("fech a_consulta"))
    servicio =DTSLookups("get_id_servicio").Execute(DTSSource(" servicio_clave"))

    If IsNull(DTSSource("profesional_clave")) Then
    msg="El profesional llego NULL"
    DTSLookups("set_entrada_log").Execute(msg)
    profesional = DTSLookups("get_id_profesional").Execute(-1)
    Else
    profesional = DTSLookups("get_id_profesional").Execute(DTSSource ("profesional_clave"))
    End If
    If IsEmpty(profesional) Then
    msg="El profesional "&CStr(DTSSource("profesional_clave"))&" no esta en la dimension"
    ' DTSPackageLog.WriteStringToLog msg
    profesional = DTSLookups("get_id_profesional").Execute(-1)
    End If

    cliente=DTSLookups("get_id_cliente").Execute(DTSSo urce("cliente_clave"))
    If IsEmpty(cliente) Then
    ' DTSPackageLog.WriteStringToLog "El cliente"&CStr(DTSSource("cliente_clave"))&" no esta en la dimension"
    cliente = DTSLookups("get_id_cliente").Execute(-1)
    End If

    cantAnio = DTSLookups("cant_consultas_año").Execute(cliente, servicio)
    If (cantAnio>0) Then
    REPETIDA = 1
    NUEVA=0
    Else
    NUEVA = 1
    REPETIDA=0
    End If

    'confirmada, asignada, nopresentada
    If StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_cit").Exec ute())=0 Then
    ASIGNADA=1
    ElseIf StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_visit").Ex ecute())=0 Then
    CONFIRMADA=1
    ElseIf StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_np").Execu te())=0 Then
    NO_REALIZADA=1
    End If

    If IsNull(DTSSource("TIPO_CEX")) Then
    ULTERIOR = 0
    PRIMERA_VEZ=0
    ElseIf CInt(DTSSource("TIPO_CEX"))=CInt(DTSLookups("get_i d_prim_vez").Execute()) Then
    PRIMERA_VEZ=1
    ElseIf CInt(DTSSource("TIPO_CEX"))=CInt(DTSLookups("get_i d_ult").Execute()) Then
    ULTERIOR = 1
    End If

    If IsNull(DTSSource("TIEMPO_ESPERA")) Then
    TIEMPO_ESPERA=-1
    Else
    TIEMPO_ESPERA=DTSSource("TIEMPO_ESPERA")
    End If


    If DTSLookups("existe_consulta_dia").Execute(cliente, profesional,servicio,fecha) = 0 Then
    'NO HAY EN LA BASE UNA CONSULTA PARA ESE CLIENTE EN ESE SERVICIO Y FECHA
    DTSDestination("CLIENTE_ID") = cliente
    DTSDestination("PROFESIONAL_ID") = profesional

    If (DTSSource("edad")<=0) Then
    DTSDestination("GR_ETAREO_ID") = DTSLookups("get_id_gr_etareo").Execute(0,0)
    Else
    If IsEmpty(DTSLookups("get_id_gr_etareo").Execute(CIn t(DTSSource("edad")),CInt(DTSSource("edad")))) Then
    DTSPackageLog.WriteStringToLog "No sabe calcular para "&CStr(DTSSource("edad"))
    Else
    DTSDestination("GR_ETAREO_ID") = DTSLookups("get_id_gr_etareo").Execute(CInt(DTSSou rce("edad")),CInt(DTSSource("edad")))
    End If
    End If


    If IsNull(DTSSource("ICD_COD")) Then
    DTSDestination("DIAGNOS_ID") = DTSLookups("get_id_diagnos").Execute(-1)
    Else
    DTSDestination("DIAGNOS_ID") = DTSLookups("get_id_diagnos").Execute(DTSSource("IC D_COD"))
    End If

    DTSDestination("SERVICIOS_ID")= servicio
    DTSDestination("FECHA_ID") = fecha

    If IsNull(DTSSource("MOTIVO_CONS_CLAVE")) Then
    DTSDestination("MOTIVO_CONS_ID")=DTSLookups("get_i d_motivo_cons").Execute(1)
    Else
    DTSDestination("MOTIVO_CONS_ID") = DTSLookups("get_id_motivo_cons").Execute(DTSSource ("MOTIVO_CONS_CLAVE"))
    End If

    If IsNull(DTSSource("PROFESIONAL_DERIV_CLAVE")) Then
    DTSDestination("PROFESIONAL_DERIVADOR_ID")=DTSLook ups("get_id_profesional").Execute(-1)
    Else
    DTSDestination("PROFESIONAL_DERIVADOR_ID")=DTSLook ups("get_id_profesional").Execute(DTSSource("PROFE SIONAL_DERIV_CLAVE"))
    End If
    DTSDestination("TIEMPO_ESPERA") = TIEMPO_ESPERA

    DTSDestination("CONSULTAS_CONFIRMADAS") = CONFIRMADA
    DTSDestination("CONSULTAS_ASIGNADAS") = ASIGNADA
    DTSDestination("CONSULTAS_NO_REALIZADAS")=NO_REALI ZADA
    DTSDestination("CONSULTAS_PRIMERA_VEZ") = PRIMERA_VEZ
    DTSDestination("CONSULTAS_ULTERIORES")=ULTERIOR
    DTSDestination("CONSULTAS_NUEVAS")=NUEVA
    DTSDestination("CONSULTAS_REPETIDAS")=REPETIDA
    DTSDestination("CONSULTAS_PROGRAMADAS")=PROGRAMADA
    DTSDestination("CONSULTAS_NO_PROGRAMADAS")=NO_PROG RAMADA
    DTSDestination("cant_consult") = 1


    msg="Inserto "&CStr(cliente)&" profesional "&CStr(profesional)&" fecha "&CStr(fecha)&" servicio "&CStr(servicio)

    'MsgBox(msg)
    Main = DTSTransformStat_OK
    Else
    'HAY EN LA BASE, SE ACTUALIZA
    a= DTSLookups("actualiza_valores").Execute(TIEMPO_ESP ERA, CONFIRMADA,ASIGNADA,NO_REALIZADA,PROGRAMADA,NO_PRO GRAMADA,PRIMERA_VEZ,ULTERIOR,NUEVA,REPETIDA,CLIENT E,PROFESIONAL,SERVICIO,FECHA)

    msg="Actualizo "&CStr(cliente)&" profesional "&CStr(profesional)&" fecha "&CStr(fecha)&" servicio "&CStr(servicio)
    'MsgBox(msg)
    Main = DTSTransformStat_SkipRow
    End If


    End Function
    Bye
    Lorena

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Re: DTS!!!!! Plese help!

    I have just looked through the code and it all looks fine.

    I suspect your problem lies elsewhere. Perhaps in the data you are processing or in the target table you are going to.

    I'm not sure what it could be though. Perhaps some sort of lock being generated on the table or database. I really don't know.

    It's possible that your MDAC installation is corrupted (it can cause some very strange behaviour). If possible try installing MDAC 2.8 and see if that helps. It won't hurt that is for sure.

    Sorry I couldn't be of more help.

Posting Permissions

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