• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1107
  • Last Modified:

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

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
0
pwanveer
Asked:
pwanveer
  • 7
  • 2
2 Solutions
 
Patrick MatthewsCommented:
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
0
 
pwanveerAuthor 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
0
 
Patrick MatthewsCommented:
pwanveer,

Try:

Function Main()
    If Trim(DTSSource("Col020").Value) = "-" Then   'this takes care of extraneous spaces
        DTSDestination("POST_BAM") = -CDbl(DTSSource("Col021").Value)
    Else
        DTSDestination("POST_BAM") = CDbl(DTSSource("Col021").Value)
    End If
    Main = DTSTransformStat_OK
End Function


Regards,

Patrick
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
pwanveerAuthor 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.
0
 
pwanveerAuthor 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
0
 
dbbishopCommented:
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
0
 
pwanveerAuthor 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
0
 
pwanveerAuthor 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
0
 
pwanveerAuthor 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
0
 
pwanveerAuthor Commented:
Thanks guys. Your responses started me down the right road.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now