SQL Server Performance Forum – Threads Archive
Generating Auto Number in DTS
Hi all,<br /><br />I am having a scenario to generate auto numbers for inserting into a table inside Transform Data Task.<br /><br />I have used column mappings for the rest of the columns. Also I have written active-x script to generate integers starting from 1 by getting the max of that field in the table. The auto numbers are generated for me but all the values are 1.<br /><br />Please find me active-x script below for generating auto numbers:<br /><br />Function Main()<br /><br />’—- CursorTypeEnum Values —-<br />Const adOpenForwardOnly = 0<br />Const adOpenKeyset = 1<br />Const adOpenDynamic = 2<br />Const adOpenStatic = 3<br /><br />’—- LockTypeEnum Values —-<br />Const adLockReadOnly = 1<br />Const adLockPessimistic = 2<br />Const adLockOptimistic = 3<br />Const adLockBatchOptimistic = 4<br /><br />Const adParamInput = &H0001<br />Const adInteger = 3<br />Const adDate = 7<br />Const adDBDate = 133<br />Const adDBTime = 134<br />Const adVarChar = 200<br />Const adVarBinary = 204<br /><br />Const adCmdText = &H0001<br />Const adCmdTable = &H0002<br />Const adCmdStoredProc = &H0004<br /><br /><br />dim SQLDBConn<br />dim BOHValue<br /><br />Set SQLDBConn = CreateObject("ADODB.Connection")<br />SQLDBConn.Open "Driver={SQL Server};SERVER=localhost;UID=ram<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />WD=ramrsm;DATABASE=ProductionResultDB"<br />’SQLDBConn.Open "Provider=SQLOLEDB.1;Data Source=MESPRODDB2; Initial Catalog=MESPRODDB1;user id = ‘MESPRD'<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />assword= ‘MESPRD’"<br /><br />On Error Resume Next<br />’ Get the max bohid from PR_BOH<br />Set rstBOH = CreateObject("ADODB.Recordset")<br />rstBOH.Open "SELECT max(BOHID)+1 as BOH FROM PR_BOH", SQLDBConn,adOpenForwardOnly,adLockReadOnly,adCmdText<br /><br />If not rstBOH.EOF then<br />If isnull(rstBOH.Fields.Item("BOH")) then<br />BOHValue=1<br />else<br />BOHValue=rstBOH.Fields.Item("BOH")<br />end if<br />end if<br /><br />DTSDestination("BOHID") = BOHValue<br /><br />Set SQLDBConn=nothing<br />set rstBOH=nothing<br /><br />Main = DTSTransformStat_OK<br /><br />End Function<br /><br />Please anybody help me out in this problem. Is there any other alternate way to achieve it.<br /><br />Thanks and Regards,<br />Ram Kumar N DI think, this line BOHValue=rstBOH.Fields.Item("BOH")
should be BOHValue=BOHValue+rstBOH.Fields.Item("BOH")
Madhivanan Failing to plan is Planning to fail
yes even i feel go with what madhivanan has suggested and change your query from:
SELECT max(BOHID)+1 as BOH FROM PR_BOH —————–to
SELECT max(BOHID) as BOH FROM PR_BOH
Why don’t you use idenity column property?
]]>