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 FunctionOriginally 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|||Hi,

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

Andrzej|||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