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 = ?)