We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

DTS Import of flat data / transform (ActiveX) if then

1,121 Views
Last Modified: 2013-11-30
DTS Import of flat (text) file / VBScript transformation (ActiveX)
Fld 20      Fld 21
-            00002500.2369
NULL     00000182.50

Fld 20 Positive / Negitive indicator
Fld 20 Number value

IF Fld 20 = - then value = Fld 21 * -1 (In other words negitive value) ELSE Fld 21 = Fld 21.

I have tried the following and recive the following error: Invalid procedure call or argument: 'DTSSource'
(the error seems to reside in this string (If (Dat)= ("-")  Then) of:

Function Main()
     DTSDestination("POST_BAM") = Value(DTSSource("Col021"))
     Main = DTSTransformStat_OK
End Function

Function Value  (Byval Dat)

Dat = DTSSource("Col020")

If (Dat)= ("-")  Then
      Value =  DTSSource("Col021") * -1
Else
         Value = DTSSource("Col021")
End If

End Function

Thank you
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Function Main()
    If DTSSource("Col020").Value = "-" Then
        DTSDestination("POST_BAM") = -DTSSource("Col021").Value
    Else
        DTSDestination("POST_BAM") = DTSSource("Col021").Value
    End If
    Main = DTSTransformStat_OK
End Function

Author

Commented:
Matthew, This looks like the correct syntax for VB but I am trying to do this using an ActiveX script via DTS as a transformation. I'm guessing the issue is with the ether the ("-") criteria not matching the values exactaly (like in the even of extra spaces or something) OR applying a mathimatical process to a text value Col21 * -1 or -Col21.
Thanks
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Patrick. I'm still getting the same error. Invalid call procedure or argument: 'DTSSource' Error on line 1.  The "Error on line 1" comment leads me to believe it has something to do with the = "-" criteria. This feild in the text file is ether - or NULL. I'm just not sure what could be causing the error.

Thanks for your help.

Author

Commented:
Is there a wild card command similer to LIKE in SQL? Is SQL terms

CASE WHEN DTSSource("Col20").Value LIKE '%-%' THEN -DTSSource("Col21").Value ELSE DTSSource("Col21").Value END AS POST_BAM
Database Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Error: Missing )
Function Main()
    If IsNull(DTSSource("Col020") Then
        DTSDestination("POST_BAM") = CDbl(DTSSource("Col021")
    ElseIf Trim(DTSSource("Col020")) = "-" Then  
        DTSDestination("POST_BAM") = CDbl(DTSSource("Col021")) * (-1)
    Else
        DTSDestination("POST_BAM") = CDbl(DTSSource("Col021"))
    End If
    Main = DTSTransformStat_OK
End Function

Changed to:
Function Main()
    If IsNull(DTSSource("Col020")) Then
        DTSDestination("POST_BAM") = CDbl(DTSSource("Col021"))
    ElseIf Trim(DTSSource("Col020")) = "-" Then  
        DTSDestination("POST_BAM") = CDbl(DTSSource("Col021")) * (-1)
    Else
        DTSDestination("POST_BAM") = CDbl(DTSSource("Col021"))
    End If
    Main = DTSTransformStat_OK
End Function

Then Errors on Error Discription: Invalid procedure call or argument: 'DTSSource' Error on line 1

Author

Commented:
I've narrowed it down to the "source" data reference in the IF statement. The following run successfully, however the criteria in the IF [If IsNull ("POST_BAM_SIGN") ] is not pulling from the right place. I think it should be [If IsNull (DTSSource("POST_BAM_SIGN")) ] or [If IsNull (DTSSource("POST_BAM_SIGN").Value)] but both fail with the [Invalid procedure call or argument: 'DTSSource' Error on line 1[ error. any suggestions would be appreciated.
Function Main()

    If IsNull ("POST_BAM_SIGN") Or IsEmpty("POST_BAM_SIGN") Then
        DTSDestination("POST_BAM") = CDbl(DTSSource("POST_BAM"))

   ElseIf Trim("POST_BAM_SIGN") = "-" Then  
        DTSDestination("POST_BAM") = CDbl(DTSSource("POST_BAM")) * (-1)

Else  DTSDestination("POST_BAM") = CDbl(DTSSource("POST_BAM"))
    End If
    Main = DTSTransformStat_OK
End Function

Author

Commented:
Got it. I had cols in the source and Destination tabs of the transformation. Once those were removed the following runs correctly.

 If (IsNull (DTSSource("POST_BAM_SIGN")) Or IsEmpty(DTSSource("POST_BAM_SIGN"))) Then
        DTSDestination("POST_BAM") = CDbl(DTSSource("POST_BAM"))

   ElseIf Trim(DTSSource("POST_BAM_SIGN")) = "-" Then  
        DTSDestination("POST_BAM") = CDbl(DTSSource("POST_BAM")) * (-1)

Else  DTSDestination("POST_BAM") = CDbl(DTSSource("POST_BAM"))
    End If
    Main = DTSTransformStat_OK
End Function

Author

Commented:
Thanks guys. Your responses started me down the right road.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.