Here's my objective in pseudo-code:
if table.comment2 LIKE '@'
and table.email ( IS NULL OR ='' )
then copy comment2 to email

If I did this in VB script, I would invoke a recordset object and step
through each row, comparing the two columns.

I was hoping that DTS transforms would assume a row-by-row operation,
but I suspect that isn't the case.
Here's my ActiveX Script:

' Copy each source column to the destination column
Function Main()

Dim x, srcCmnt, dstEmail
dstEmail = DTSDestination("email")
srcCmnt = DTSSource("comment2")

If IsNull (srcCmnt) Then
Main = DTSTransformStat_OK
Exit Function
End If

For x=1 to Len (srcCmnt)
If Mid ( srcCmnt , x , 1 ) = "@" Then
If ( IsNull (dstEmail) Or dstEmail="" ) Then
DTSDestination("email") = DTSSource("comment2")
End If
End If
Next

Main = DTSTransformStat_OK
End Function


It doesn't do anything!

Can someone point me in the right direction?
Is there a DTS equivalent of the recordset object? I've heard
something about a 'datapump'.
I'd love some keywords with which to search the help, or some lines of
code if I'm almost there.

Thanks in advance!

DevNull,

I'm kinda green at this stuff too, but here are my thoughts.

First, are these two fields in the same table already? From your pseudo-code, it appears that way. If that is the case, then my
guess is that you would just need to run an update query to copy the field contents from one to the other.

If this is a matter of copying the email address from 1 field in the source file/table to 2 fields in the destination table during
import then I would adjust your code as such:



' Copy each source column to the destination column
Function Main()

Dim x, srcCmnt, dstEmail
dstEmail = DTSDestination("email")
srcCmnt = DTSSource("comment2")

'***Completely remove the following section. It seems unnecessary.***
' If IsNull (srcCmnt) Then
' Main = DTSTransformStat_OK
' Exit Function
' End If

'***This section would be eliminated or...***
' For x=1 to Len (srcCmnt)
' If Mid ( srcCmnt , x , 1 ) = "@" Then
' If ( IsNull (dstEmail) Or dstEmail="" ) Then
' DTSDestination("email") = DTSSource("comment2")
' End If
' End If
' Next

'***...changed to the this section***
If not IsNull (srcCmnt) Then
If InStr(srcCmnt, "@") > 0 Then
If IsNull(dstEmail) or dstEmail="" Then
DTSDestination("email") = DTSSource("comment2")
End If
End If
End If

Main = DTSTransformStat_OK
End Function




Also, if this is a matter of copying 1 field in a source to 2 fields in a destination, you can include both destination fields in
this transformation so it would take care of both in one step.

I hope this helps,

Conan Kelly


"DevNull" <devnll2002 (AT) yahoo (DOT) com> wrote

Hello DevNull,

I would try not to do this in an Active X transform I would look to do this
in the SourceSQLStatement if possible



So your example might look something like

SELECT
...

CASE WHEN Comment2 LIKE '@%' AND (Email IS NULL OR Email ='') THEN Comment2
ELSE 'Something' END as Email

....





Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks to both of you!

My thoughts cleared over the weekend, and with your hints I realized
that a simple UPDATE query in a stored procedure called from code will
suffice.

I was fixated on DTS because a DTS package imports the data, and I
thought it could be done in the same step--and maybe it can be with the
SourceSQLStatement method of a DataPump object(?) I'll need to read
more on that.