VBScript Creating Record Numbers from a Text File Import

Posted on 2006-04-14
Last Modified: 2008-02-01
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.


Question by:blouckswwu
    LVL 2

    Assisted Solution

    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.

    LVL 75

    Accepted Solution

    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

    Author Comment

    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!
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>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

    Author Comment

    Then let me restate my statement:  "Can you give me a little more information?"

    The links was what I was looking for.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now