SQL Server Performance

Lookup Transaction Issue

Discussion in 'SQL Server DTS-Related Questions' started by RDAGLISH, Aug 9, 2004.

  1. RDAGLISH New Member

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

Share This Page