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.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
Anthony PerkinsCommented:
>>Can you give me a little direction?<<
I believe I did.  I any case please take a look at the following site:

And in particular the following links:
Introduction To Global Variables
blouckswwuAuthor Commented:
Then let me restate my statement:  "Can you give me a little more information?"

The links was what I was looking for.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.