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

on
1,121 Views
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

## View Solutions Only

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

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)

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.

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)

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

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

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

Commented:
Unlock the solution to this question.