Does DTS (or the OLEDB driver it uses) import rows from txt or xls files in their physical sequence in the file? Some experimenting doesn't turn up any instances where it didn't, but I need to be sure that the destination work table will always receive the data in the order it exists in the file (so I can link header and detail rows using an identity column in the destination table). Thanks in advance.

No there is no guarantee of the order in which rows get inserted. If you
need to link header and details you should not use an Identity.

--
Andrew J. Kelly SQL MVP


"Eric Jerome" <Eric Jerome (AT) discussions (DOT) microsoft.com> wrote

any instances where it didn't, but I need to be sure that the destination
work table will always receive the data in the order it exists in the file
(so I can link header and detail rows using an identity column in the
destination table). Thanks in advance.

Thanks. I didn't want to write automatic line numbering code for the files if it wasn't necessary - looks like it is.

"Andrew J. Kelly" wrote:

I usually import files like this into a staging table and then from there do
the inserts into the production ones. That way you can control such things
as inserts into a table with Identity columns. You can insert the header,
get the Identity with Scope_Identity() and use it to insert the details.

--
Andrew J. Kelly SQL MVP


"Eric Jerome" <EricJerome (AT) discussions (DOT) microsoft.com> wrote

'Good thing Joe Celko doesn't read this group.


"Eric Jerome" <Eric Jerome (AT) discussions (DOT) microsoft.com> wrote

any instances where it didn't, but I need to be sure that the destination
work table will always receive the data in the order it exists in the file
(so I can link header and detail rows using an identity column in the
destination table). Thanks in advance.

Actually, since there is no data in the file that clearly distinguishes which header information goes with which detail information other that the sequence in which they appear in the file, I have to use VB / VBA code to add line numbers to the files before they are imported . Otherwise DTS may shuffle the rows and I will have no way of telling which detail rows go with which header rows. For example, a file may have customer information on the first row followed by several rows of invoice information for that customer's invoices (but no customer ID or other field to link the two types of rows with). Then there would be another customer row followed by more invoice rows that go with that second customer, etc...

"Andrew J. Kelly" wrote:

Can't you request they provide the key to be included int he file when it is
generated? How does the other system know what goes with what? That's a
pretty absurd way to match records and prone for failure.

--
Andrew J. Kelly SQL MVP


"Eric Jerome" <EricJerome (AT) discussions (DOT) microsoft.com> wrote

sequence in which they appear in the file, I have to use VB / VBA code to
add line numbers to the files before they are imported . Otherwise DTS may
shuffle the rows and I will have no way of telling which detail rows go with
which header rows. For example, a file may have customer information on the
first row followed by several rows of invoice information for that
customer's invoices (but no customer ID or other field to link the two types
of rows with). Then there would be another customer row followed by more
invoice rows that go with that second customer, etc...

But I do scan for my name every now and then