I'm trying to load a sql server 2000 table with a text file where the table needs to be in the order of the .txt file. Each line of text is a record in the table. Sometimes the lines load in proper order, but more often they don't. How can I force the lines to load in the proper order? It's done in a DTS package, but I also get the same problem when I load it using the DTS wizard. Each row in the table is a line of text, so there's nothing I can use to order by

Can you post a sample few lines from the file? That way I can see exactly what you mean. Just edit out important information with XXXXX if necessary when you post it. Thanks.

Here's a sample. (data may have wrapped to another line) There can be more than 20 pages in the file. Each line in the file is loaded as a row in the table. The order is important because information for a customer is grouped in the 5 lines beginning w/ the one above My NAME. I look for keywords and take information from 3 of the lines to use in subsequent steps. If they aren't loaded in this exact order, customer information gets mixed up. Sometimes the file loads properly, but usually it doesn't (eg. Part of page 3 is loaded before the beginning of the file, etc). Could it be a buffering problem?

PageHeading1 :06-04-2006 DATE:06-04-2006 TIME:12:39:36 PAGE: 1
Report Title
Report date 06-04-2006
Other Heading Line

T TYPE CODE AMOUNT DATE TIME Rep Comment
----------------------------------------------------------------------------------
Q XXXXXXXXXXXXXXXXXxxxxxx 21.36 06-04 11:36 028287 xxx xxxxx xxxxx xxxx xx xx xxx
My NAME : Joe Smith
My Customer# : 12345678
My zip code : 12345
My email : xxx@xxx.xxx
C XXXXXXXXXXXXXXXXXxxxxxx 10.00 06-04 11:36 028288 xxx xxxxx xxxxx xxxx xx xx xxx
My NAME : Bob Smith
My Customer# : 213456789
My zip code : 23456
My email : xxxx@xxx.xxx
G XXXXXXXXXXXXXXXXXxxxxxx 24.46 06-04 11:36 028289 xxx xxxxx xxxxx xxxx xx xx xxx
My NAME : Roger Smith
My Customer# : 34567
My zip code : 44646
My email : xxxxxxxx@xxxxx.xxx
H XXXXXXXXXXXXXXXXXxxxxxx 31.95 06-04 11:36 028290 xxx xxxxx xxxxx xxxx xx xx xxx
My NAME : Sue Smith
My Customer# : 98765
My zip code : 49876
My email : xxxxxxxxxxxxxxxx@xxxxxxxxxxxxxxx.xx
Q XXXXXXXXXXXXXXXXXxxxxxx 21.16 06-04 11:36 028291 xxx xxxxx xxxxx xxxx xx xx xxx
My NAME : Nancy Smith
My Customer# : 87654
My zip code : 43522
My email : xxxxxxxxxxxxxxxxxxxxx@xxxxx.xxx
F XXXXXXXXXXXXXXXXXxxxxxx 39.00 06-04 11:36 028292 xxx xxxxx xxxxx xxxx xx xx xxx
My NAME : Terry Smith
My Customer# : 22732
My zip code : 33511
My email : xxxxxxx@xxxxxxxxxxxxx.xxx

What are you using to import the data? It looks pretty standard from what I can tell..

I'm using a DTS package in SQL Server 2000. I also tried to load it using the DTS wizard and get the same result - sometimes it loads ok, sometimes it doesn't.

Are you able to break it up into multiple files and import them individually? It might be that the file is too big and is using too many system resources when importing it.

No, it can't be broken up into smaller files. Could it be a buffering problem? The DTS pkg had a row size of 8000. I switched it to 255 & played around w/ other numbers, but it didn't seem to help.

Are yo just tring to place the info in the correct place? so you can search for it later?

I'm trying to load the text file in the original order so I can search for keywords to extract information on different lines for a particular customer. Customer information is grouped together over several lines, so I need it to be in the order it occurs in the text file