changing text file imports from bcp on sql6.5 to dts on sql2000
-
I have some processes on an mssql6.5 server that import & process large flat text files (mainly letters files, 60,000+ lines). I used BCP to import these text files into a table on my sql server so I could run some scripts to alter or process some of the data in the text & then output the whole file with amendments.
I recently moved my batch scripting operations to an MSSql2000 server and have noticed that if I use BCP or DTS, the imported data is not in its original order, whereas it is on mssql6.5. This means I have to keep the 6.5 database running until I can find a solution to this ordering business.
Is this a bug in DTS/BCP? Or, is there something I could do to view the data , once imported on mssql2000, in its original order? Or are there any good freeware utilities out there that can generate a line number prefix or suffix on a text file to enable me to order the file once it's imported?
thanks in advance fior any helpful suggestions.
-
I have found a work-round for now. It's not perfect but it does the job.
I can use DTS to import the textfiles into an access database with an auto-incrementing row number field and then export the data in the same package from access to my sql2000 database. The result is the data passed to the DB table in the same order that it appears in the flat file.
-
It's not a problem with DTS at all. The data is loaded in same order as on the source file. It is the select that displays the loaded text file in no particular order.
So easy to forget these little things.
Prefixing an identity column onto my table has cured the problem so I can import from a text file into a DB table and display the data in its intended order. Brilliant!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply