DTS Loads Text File in wrong sequence

[amcalpine]

I am trying to load a text file into a temporary table in MS-SQL using DTS.

The file is coming from a unix machine and contains comma-delimited entries.

the DTS script runs every minute and the transformation maps values in the text file into corresponding columns in the temp table.

The problem is that the sequence of the entries in the text file does not match the sequence of the values imported.

I have several instances of this script (all identical) but only one file seems to be out of whack. Does anyone know what could be causing this? This is for a real-time trading system so the sequence has to be perfect.

Thanks
Alastair

[MAK]

Did you create index on the temp table before inserting any data or did you use order by clause?

Does your source having any unique column which is incremental?

[amcalpine]

I have tried two options:
1 - temp table with no sequence
2 - temp table with sequence

Neither approach seems to make a difference.

the script flow is simple enough:
1 - delete from temptable
2 - import from text file
3 - use a view in the db to export to file

The source has no unique column unfortunately. I am using a sequence in the temp table on the assumption that the text file would be loaded sequentially into the table, so I can then sort based on the sequence when I output the file, but no luck.

I even specified Table Lock on with the Fast Load option in the transform data task; this appeared to fix it but then after the file reaches a certain size the sequence goes AWOL again.

Is there something in how SQL would handle a file coming from a Unix source?

[Stephen]

What do you mean by "sequence" ?

If I need to preserve the order of the records being imported from a text file, I import into a table that has a column that has the identity property. This has never failed me.

[amcalpine]

That's what I am using. Here's the script for the table:

CREATE TABLE [dbo].[Tab_GLExport_Asia] (
[Sequence] [bigint] IDENTITY (1, 1) NOT NULL ,
[UTI] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Memo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Expiry] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContractType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StrikePrice] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TradeDate] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Price] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Side] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Place] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Market] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mnemonic] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FEx] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Firm] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NoCTE] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NIA] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

[Rawhide]

Are you importing just one text file each time or multiple text files?

[amcalpine]

Just one file at a time.

It's strange. even re-creating a fresh DTS package to import the data (about 3400 rows) into a table, and creating the table based on the file, this also enters the records into the table in the wrong sequence.

Info: I am running SQL 2000 SP3 (8.00.760) on Windows 2000 Advanced Server (Clustered). Could there be a patch available which may improve things with DTS?

Sorry but I'm just a beginner in the whole DBA scheme of things !!!

[Rawhide]

No, this isn't an issue like that. It has nothing to do with clustering.

Make your Sequence field a primary key (or simply add a unique clustered index on the field).

What is the SQL contained in the view?

When you say DTS script, what do you mean? Do you mean DTS package or is there some script that runs?

[amcalpine]

Thanks Rawhide .. you're a legend.

The sequence is back to normal now after I added the Primary Key. I will see if this issue comes back and post another message if it's still awry.

BTW To answer your other questions:

1- It's a DTS Package
2 - The view links four tables, with a couple of OUTER JOINS and one Expression using a combo of ISNULL and STRING functions to perform a lookup. (A pretty neanderthal approach but it works).