Help using RegExp in a column Transformation

[DanC]

I'm still in learning mode with regular expressions and am attempting a few things during a Trasformation task from one table to another. Basically what I'm trying to to is this: during the copy/import from one table to another, I'm trying to somewhat validate an email field. When it returns valid I want it mapped directly to the corresponding column in the destination, but when it doesn't match to the expression, I want it moved to a different column. What I've got so far is this:

Function Main()

Dim objRE
Dim strExample
set objRE = new RegExp
objRE.Pattern = "^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,})+$"
strExample = TRIM( DTSSource("CAP_EMAIL") )
' Test if a match is found
If objRE.Test(strExample) = True Then
DTSDestination("CAP_EMAIL") = DTSSource("CAP_EMAIL")
Else
DTSDestination("invalidCapEmail") = DTSSource("CAP_EMAIL")
End If
Set objRE = Nothing

Main = DTSTransformStat_OK
End Function

It keep returning the following error: ActiveX Scripting Transform '<DTS Testing Transformation>': Function 'reg' was not found in the script.

Any help would be appreciated.

-Dan

 

[yksvaan]

Can't say about your code but if it's a syntax issue the following example works (it is not a transformation), maybe you get some help out of it. Of course, you need VBScript.RegExp library on our computer.

Function RegExpTest(patrn, strng)
   Dim regEx, Match, Matches   ' Create variable.
   Set regEx = New RegExp   ' Create a regular expression.
   regEx.Pattern = patrn   ' Set pattern.
   regEx.IgnoreCase = True   ' Set case insensitivity.
   regEx.Global = True   ' Set global applicability.
   Set Matches = regEx.Execute(strng)   ' Execute search.
   For Each Match in Matches   ' Iterate Matches collection.
      RetStr = RetStr & "Match found at position "
      RetStr = RetStr & Match.FirstIndex & ". Match Value is '"
      RetStr = RetStr & Match.Value & "'." & vbCRLF
   Next
   RegExpTest = RetStr
End Function

Function Main()
MsgBox(RegExpTest("is.", "IS1 is2 IS3 is4"))

	Main = DTSTaskExecResult_Success
End Function

Cheers

[blue]Backup system is as good as the latest recovery[/blue]

 

[DanC]

Thanks for your reply. It doesn't appear to be a sytax issue, as I can run the same code in a DTS ACtiveX script task and replace the column transformations with MsgBox's and have it work successfully.