Unique Key Insert with DTS & VB Script.

[DaveRolph]

I am using SQL 2000 and have used the DTS to do straight import / exports for some time now.

Now have a massive data set that I need to import (5 Million records), the trouble is it needs cleaning, in the past I would insert to a Dummy SQL Table and write a TSQL Script to clean it.

Now I would really like to have a go with the VBScript ActiveX Option and I am quite a bit stuck at the first hurdle.

The first part to this should be simple, I want to create a unique key but this must be 8 Characters long padded wth zeros, i.e 00000001,00000002 etc.

I created a DTS Package and my ever so basic script is as follows...

Function Main()
DTSDestination("Details") = DTSSource("NOTE-DESC")
DTSDestination("Entered_By") = DTSSource("GN-AUTHOR")
DTSDestination("Entered_Date") = DTSSource("GN-DATE")
DTSDestination("Gen_Notes_ID") = "000001"
DTSDestination("From_DB") = 3
Main = DTSTransformStat_OK
End Function

Now the Gen_Notes_ID Is always 000001 at the moment (obviously) but can anyone give me some advice to autu-incrementing.

I did have a good search on the forum but could find nothing.

Thanks for any replies - sorry its long.

Dave

 

[DaveRolph]

OK I found a way, works well, does anyone have any advice / comments ?

I will post my next problem in another thread.


Dim tSeq
Dim tCleanSeq

Function Main()
tSeq = tSeq + 1
tCleanSeq=Left("00000000",(8-Len(tSeq)))&tSeq
DTSDestination("Details") = DTSSource("NOTE-DESC")
DTSDestination("Entered_By") = DTSSource("GN-AUTHOR")
DTSDestination("Entered_Date") = DTSSource("GN-DATE")
DTSDestination("Gen_Notes_ID") = tCleanSeq
DTSDestination("From_DB") = 3
Main = DTSTransformStat_OK
End Function

 

[PHV]

I'd use this:
tCleanSeq = Right("0000000" & tSeq, 8)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DaveRolph]

Great PHV that's neater I will use that.

get ready for the next one ;-)