VBScript Creating Record Numbers from a Text File Import

I am creating a fairly simple DTS package (using VBScript) that will take a file that was FTP'd from a vendor site once a month.  Then this file will be imported into a staging table so our Accounts Rec person can process it.  There is a place for a sequence number and I want to put in a record number for the import.  I am clueless on how to generate this on the fly while processing records.

Thanks

Bill
blouckswwuAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
Can you use an IDENTITY column so that each row has an incremental value as suggested in the previous comment?

Otherwise you can do the folllowing:
1. Create a Global Variable SequenceNumber as integer
2. Set it to 0 in an ActiveX Script Task at the start of your DTS Package.
3. Convert the transformation to an ActiveX Script and in the code increment the value of SequenceNumber as appropriate.
4. Assign the value of SequenceNumber to the Destination Sequence Number column
0
 
knucklesbammConnect With a Mentor Commented:
Hey Bill,

I recommend inserting the data from the file you grab from your vendor site into a temporary table variable that you create with an indentity column on it. This identity column can be the record number for the import.

HTH
0
 
blouckswwuAuthor Commented:
Sorry for the delay in responding to this thread.  Due to lack of knowledge on DTS (outside of the wizard), I've been trying to read about Global Variable's in DTS.  I am having a hard time finding information about this on the Internet that would have a good coding example of how to go about creating and using a Global Variable.  Can you give me a little direction?

Thank you!
0
 
Anthony PerkinsCommented:
>>Can you give me a little direction?<<
I believe I did.  I any case please take a look at the following site:
http://www.sqldts.com

And in particular the following links:
Introduction To Global Variables
http://www.sqldts.com/default.aspx?280
0
 
blouckswwuAuthor Commented:
Then let me restate my statement:  "Can you give me a little more information?"

The links was what I was looking for.
0
All Courses

From novice to tech pro — start learning today.