Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    27

    Unanswered: DTS Workflow problems

    I have created a stored procedure, but returns a recordset if sorten conditions are met.
    The stored procedure can return no recordset as well. Then, I have created a DTS that executes
    this stored procedure. What I want is the following. If the stored procedure does not return anything, I want the DTS not to execute.
    My DTS consists of: Connection > Destination File.

    This is my ActiveX transformation:

    Function Main()
    DTSDestination("Study_Area_ID") = DTSSource("Study_Area_ID")
    DTSDestination("Entered_Month_Dt") = DTSSource("Entered_Month_Dt")
    DTSDestination("Applies_To_Month_Dt") = DTSSource("Applies_To_Month_Dt")
    DTSDestination("Support_Type_Cd") = DTSSource("Support_Type_Cd")
    DTSDestination("LSS_Payment_Amt") = DTSSource("LSS_Payment_Amt")
    DTSDestination("LSS_Calc_Payment_Amt") = DTSSource("LSS_Calc_Payment_Amt")
    DTSDestination("Eligibility_Ind") = DTSSource("Eligibility_Ind")

    Main = DTSTransformStat_OK
    End Function

  2. #2
    Join Date
    Sep 2002
    Location
    MI
    Posts
    19

    Re: DTS Workflow problems

    Originally posted by awegrzyn
    I have created a stored procedure, but returns a recordset if sorten conditions are met.
    The stored procedure can return no recordset as well. Then, I have created a DTS that executes
    this stored procedure. What I want is the following. If the stored procedure does not return anything, I want the DTS not to execute.
    My DTS consists of: Connection > Destination File.

    This is my ActiveX transformation:

    Function Main()
    DTSDestination("Study_Area_ID") = DTSSource("Study_Area_ID")
    DTSDestination("Entered_Month_Dt") = DTSSource("Entered_Month_Dt")
    DTSDestination("Applies_To_Month_Dt") = DTSSource("Applies_To_Month_Dt")
    DTSDestination("Support_Type_Cd") = DTSSource("Support_Type_Cd")
    DTSDestination("LSS_Payment_Amt") = DTSSource("LSS_Payment_Amt")
    DTSDestination("LSS_Calc_Payment_Amt") = DTSSource("LSS_Calc_Payment_Amt")
    DTSDestination("Eligibility_Ind") = DTSSource("Eligibility_Ind")

    Main = DTSTransformStat_OK
    End Function


    Use the following active x transformation task. Make sure you add all of the source and destination colums. I do this a lot please let me know if you need help. jon@oratu.com


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

    ' Copy each source column to the destination column
    Function Main()


    if var is DTSSource("Study_Area_ID") and DTSSource("Entered_Month_Dt") then
    Main = DTSTransformStat_SkipRow
    else

    DTSDestination("Study_Area_ID") = DTSSource("Study_Area_ID")
    DTSDestination("Entered_Month_Dt") = DTSSource("Entered_Month_Dt")
    DTSDestination("Applies_To_Month_Dt") = DTSSource("Applies_To_Month_Dt")
    DTSDestination("Support_Type_Cd") = DTSSource("Support_Type_Cd")
    DTSDestination("LSS_Payment_Amt") = DTSSource("LSS_Payment_Amt")
    DTSDestination("LSS_Calc_Payment_Amt") = DTSSource("LSS_Calc_Payment_Amt")
    DTSDestination("Eligibility_Ind") = DTSSource("Eligibility_Ind")

    main = Main = DTSTransformStat_OK
    end if

    End Function
    Jon Nelson

  3. #3
    Join Date
    Mar 2003
    Posts
    27

    Thanks

    Hi,

    I'll try this at work tomorrow and let you know. Thanks for the help.

    Andrzej

  4. #4
    Join Date
    Mar 2003
    Posts
    27

    Re: DTS Workflow problems

    Hi,

    Your code failed on line 1 (object required) during execution
    This is what i had:

    Function Main()
    IF var is DTSSource("Study_Area_ID") and DTSSource("Entered_Month_Dt") THEN
    Main = DTSTransformStat_SkipRow
    ELSE
    DTSDestination("Study_Area_ID") = DTSSource("Study_Area_ID")
    DTSDestination("Entered_Month_Dt") = DTSSource("Entered_Month_Dt")
    DTSDestination("Applies_To_Month_Dt") = DTSSource("Applies_To_Month_Dt")
    DTSDestination("Support_Type_Cd") = DTSSource("Support_Type_Cd")
    DTSDestination("LSS_Payment_Amt") = DTSSource("LSS_Payment_Amt")
    DTSDestination("LSS_Calc_Payment_Amt") = DTSSource("LSS_Calc_Payment_Amt")
    DTSDestination("Eligibility_Ind") = DTSSource("Eligibility_Ind")

    Main = DTSTransformStat_OK
    END IF
    End Function

Posting Permissions

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