I would like to know if there is a way to reference the value of a field
while importing data into a table.

I am loading data from a flat file into a SQL Server table using a custom
ActiveX transformation. I set the value of the table field to a value from
the flat file. Can I immediately reference the value of the field from the
code?

Here is a psuedo-code example of what I would like to do. The first example
is what I would like to do (get the value I just set). The second example is
code that I know works.

--does not work
If IsNumeric(DTSSource("Col010")) Then
DTSDestination("Pieces") = CLng(DTSSource("Col010"))
If DTSDestination("Pieces") <= 0 Then
DTSDestination("Bad") = 10 'pieces must be greater than 0
End If
Else
DTSDestination("Bad") = 14 'invalid character in numeric field
End If

--works
If IsNumeric(DTSSource("Col010")) Then
DTSDestination("Pieces") = CLng(DTSSource("Col010"))
If CLng(DTSSource("Col010")) <= 0 Then
DTSDestination("Bad") = 10 'pieces must be greater than 0
End If
Else
DTSDestination("Bad") = 14 'invalid character in numeric field
End If


The reason I would like to reference the table in memory is because the
second option forces me to perform the clng function twice. Since I will be
loading 1,000,000+ records each time the DTS runs, there will be a
performance impact. Please advise if the first example is possible. Thanks!


--
Lionel Horn

Hello,

You can assign the value of CLng(DTSSource("Col010")) to a variant to
resolve the issue. I have tested the method on the Categories table in the
Northwind database and it works fine.

For your reference, the test code is as followings:

Function Main()

Dim field
field=DTSSource("CategoryID")

If field <0 then

DTSDestination("CategoryID") = 0

else

DTSDestination("CategoryID") =field

end if

DTSDestination("CategoryName") = DTSSource("CategoryName")
DTSDestination("Description") = DTSSource("Description")
DTSDestination("Picture") = DTSSource("Picture")
Main = DTSTransformStat_OK
End Function

I hope the information is helpful.


Sophie Guo

Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.