>Would you post the activex script?
>
>-Mike

'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'**********************************************************************
Dim cnErrorsConnection
Dim cmdInsertError
Dim oBizProcs

Set cnTransConnection = DTSGlobalVariables("TransConnection").Value
Set cmdInsertTrans = CreateObject("ADODB.Command")

Set oBizProcs = CREATEOBJECT( "BizProcs.MerkleUtils" )

Function Main()
Dim iNewTransactionId

' Initialize the error code and transaction id for the each transaction row
iErrorCode = 0
iNewTransactionId = CInt(DTSGlobalVariables("LastTransactionId").Value) + 1

DTSDestination("TransactionId") = cStr(iNewTransactionId)
DTSDestination("person_id") = DTSSource("Col001")
' DTSDestination("previd") = DTSSource("Col002")
' DTSDestination("hholdid") = DTSSource("Col003")
' DTSDestination("matchcode") = DTSSource("Col004")
DTSDestination("company") = DTSSource("Col005")
DTSDestination("lname") = RTrim(LTrim( Cstr( DTSSource("Col006")) ))
DTSDestination("fname") = RTrim(LTrim( Cstr( DTSSource("Col007")) ))
DTSDestination("midinit") = RTrim(LTrim( DTSSource("Col008")))
DTSDestination("prefix") = RTrim(LTrim( DTSSource("Col009")))
DTSDestination("suffix") = DTSSource("Col010")
DTSDestination("address1") = Left(cStr( DTSSource("Col011") ),30)
DTSDestination("address2") = Left(cStr( DTSSource("Col012") ),30 )
DTSDestination("city") = Left(DTSSource("Col013"),16)
DTSDestination("state") = DTSSource("Col014")
DTSDestination("zip") = cStr( DTSSource("Col015") ) & cStr( DTSSource("Col016") )
DTSDestination("plus4") = DTSSource("Col016")
' DTSDestination("namectg") = DTSSource("Col017")
' DTSDestination("cart") = DTSSource("Col018")
' DTSDestination("dpbc") = DTSSource("Col019")
' DTSDestination("dpck") = DTSSource("Col020")
' DTSDestination("fips") = DTSSource("Col021")
' DTSDestination("phone") = DTSSource("Col022")
' DTSDestination("dayphone") = DTSSource("Col023")
' DTSDestination("coadateyyyy") = DTSSource("Col024")
' DTSDestination("coadatemm") = DTSSource("Col025")
' DTSDestination("coadatedd") = DTSSource("Col026")
' DTSDestination("acecode") = DTSSource("Col027")
' DTSDestination("acs") = DTSSource("Col028")
' DTSDestination("acsia") = DTSSource("Col029")
' DTSDestination("acsib") = DTSSource("Col030")
' DTSDestination("acs2a") = DTSSource("Col031")
' DTSDestination("acs2b") = DTSSource("Col032")
' DTSDestination("astatus") = DTSSource("Col033")
' DTSDestination("mflag") = DTSSource("Col034")

' ' Concatenate the NCOA Date
' DTSDestination("coadate") = DTSDestination("coadatemm") + "/" + DTSDestination("coadatedd") + "/" + DTSDestination("coadateyyyy")

' ' Get gender
' DTSDestination("gender") = oBizProcs.DeriveGender(cStr( DTSDestination("fname") ))

' Process prefix and first name
If inStr(1,cStr(DTSDestination("fname"))," ") > 0 Then
DTSDestination("fname") = oBizProcs.DerivePrefix(cStr( DTSDestination("fname") ))
If inStr(1,cStr(DTSDestination("fname")),"~") > 0 Then
DTSDestination("prefix") = Left( cStr(DTSDestination("fname")), inStr(1,cStr(DTSDestination("fname" )) ,"~") - 1)
DTSDestination("fname") = Right( cStr(DTSDestination("fname")), Len(cStr(DTSDestination("fname" )))- inStr(1,cStr(DTSDestination("fname" )) ,"~"))
End if
End if

' Process suffix and last name
If inStr(1,cStr(DTSDestination("lname"))," ") > 0 Then
DTSDestination("lname") = oBizProcs.DeriveSuffix(cStr( DTSDestination("lname") ))
If inStr(1,cStr(DTSDestination("lname")),"~") > 0 Then
DTSDestination("suffix") = Right(cStr(DTSDestination("lname")), Len(cStr(DTSDestination("lname"))) - inStr(1,cStr(DTSDestination("lname" )) ,"~"))
DTSDestination("lname") = Left( cStr(DTSDestination("lname")), inStr(1,cStr(DTSDestination("lname" )) ,"~") - 1)
End if
End if

DTSDestination("lname") = Left( DTSDestination("lname"), 13 )
DTSDestination("fname") = Left( DTSDestination("fname"), 13 )


DTSGlobalVariables("LastTransactionId").Value = CInt(iNewTransactionId)

Main = DTSTransformstat_UserQuery ' Populate the NCOAInput table

InsertTrans DTSGlobalVariables("LastTransactionId").Value, _
DTSDestination("company"), _
DTSDestination("prefix"), _
DTSDestination("suffix")


End Function

Sub InsertTrans(Transaction_ID, Company, Prefix, Suffix)
If trim(company) = "" then
company = chr(34) & " " & chr(34)
End if
If trim(prefix) = "" then
prefix = chr(34) & " " & chr(34)
End if
If trim(suffix) = "" then
suffix = chr(34) & " " & chr(34)
End if

msgbox "sp_InsertTransactions " & cLng(Transaction_ID ) & "," & company & "," & prefix & "," & suffix

cmdInsertTrans.ActiveConnection = cnTransConnection
' cmdInsertTrans.CommandText = "sp_InsertTransactions " & cLng(Transaction_ID ) & "," & company & "," & prefix & "," & suffix

cmdInsertTrans.CommandText = "sp_InsertTransactions " & cLng(Transaction_ID ) & "," & "a*b" & "," & prefix & "," & suffix


((((NOTE: In the line above I substituted the constant "a*b" for testing.))))

cmdInsertTrans.Execute
End Sub


Bob