SQL Server Performance Forum – Threads Archive

Lookup Transaction Issue

I am transferring users records from a ‘Users’ table to a ‘NewUsers’ table in the same database. This involves 2 connections (pointing to the same server/database) and a transformation task sat between the two. If a user already exists within ‘NewUsers’ the idea is suffix the username, ie FredBloggs becomes FredBloggs2 or FredBloggs3… This is acheieved by a lookup object within the transform. This works in principal and is syntactically ok. However, the lookup isnt seeing the records processed within the transform. I believe this has something to do with the fact that these are not within the same connection/transaction scope. how can i make the ‘Lookup’ take account of records created with the transform? Hope this makes some sense. Regards
Richard
PS
The transform script
‘ Copy each source column to the destination column
Function Main()
dim sNewUsername ‘*** while username exists
dim arUsernameCount, Index
Index=0
Do
Index=cint(Index)+1
if cint(Index) =1 then
sNewUsername= Trim( Mid( DTSSource("USERNAME") , 4 , len(DTSSource("USERNAME")) ) )’Strip first 3 chars ie A38
else
sNewUsername= Trim( Mid( DTSSource("USERNAME") , 4 , len(DTSSource("USERNAME")) ) & Index ) ‘Strip first 3 chars ie A38 + Index
end if
sNewUsername=sNewUsername
arUsernameCount = DTSLookups("UsernameExists").Execute(sNewUsername) Loop Until arUsernameCount=0 ‘Does name exist msgbox sNewUsername
DTSDestination("FullName")=mid(sNewUsername, 1, 1 ) & " " & mid(sNewUsername, 2, len(sNewUsername) )
DTSDestination("USERNAME") = sNewUsername
DTSDestination("CreatedBy")=DTSGlobalVariables("AdminUserID").Value
Main = DTSTransformStat_OK
End Function
The lookup script SELECT COUNT(Username) AS CountUsernames
FROM LOL_User
WHERE (Username = ?)

---

]]>