Problem while Import flat file data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Problem while Import flat file data

Author

Topic

imsganesh
Starting Member

28 Posts

Posted - 2005-05-03 : 12:44:03

Hi,

I've a DTS Package which is importing a flat file's data into a SQL Server table. When I run the package, its failing in a particular line of the file. The error msg that i got was "Non-white space character found..." But, when i checked the line, it is same as any other line in the file (no diff.).

For eg., its inserting 1002 records & failing at 1003rd record. If I removed the first 500 lines (or 1000 lines) & run the package, its working properly & inserting the 1003rd record as well. Also, its not the problem of no. of records. Coz, sometimes, the package is running properly for larger number of data (even for bigger files).

I donno why its failing? Is it something to do with Commit counter or something of that sort?

Any suggestions pls!

Thanks,
Ganesh

mfemenel
Professor Frink

1421 Posts

Posted - 2005-05-03 : 14:09:25

welcome to hell. Population...you. This is an annoying problem I've had before. First thing is to check for carriage return linefeed characters. A few methods to try are opening it up in visual studio if you have it, or word pad. One trick you might try which worked well for me was trying to import the file into excel. The interface in excel is going to hit the same issue. That sometimes give you a clearer idea of where it is. Typically it's 1 record that something didn't quite turn out right in and it throws you off. Unforunately it's one of those "jiggle the handle" problems where you end up playing with it until you find/correct the offending record.

Mike
"oh, that monkey is going to pay"

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-03 : 14:59:59

For all the ohhhs and ahhhs DTS evokes, it aint worth it

Try this

CREATE TABLE myTable99(Col2 varchar(8000))

bcp the data in to that table

Then

CREATE TABLE myTable99_a(Col1 int IDENTITY(1,1), Col2 varchar(8000))

Then

INSERT INTO myTable99_a(Col2) SELECT Col2 FROM myTable99

Then

SELECT Col2 FROM myTable99_a WHERE Col1 BETWEEN 950 AND 1050

Now there is no gaurentee that the data will load in the order of the file order...BUT, I have yet to see it not do it.

In any event you can probably check for some strange values.

Where is the file coming from.

Brett

8-)

imsganesh
Starting Member

28 Posts

Posted - 2005-05-03 : 17:44:41

Thanks Mike/Brett!!
The problem here is, when I split the files into 2 & run it, its running properly. As stated above, if i run it for first 1000 recs (removing all after 1000) & then again run from 1001 to the end of the file...its running!!! Anyway, I'll try all the options that you said & post it here!!!

Thanks,
Ganesh

imsganesh
Starting Member

28 Posts

Posted - 2005-05-03 : 19:06:25

Mike, I was able to import the file into Excel. Brett, I also tried using the tables with identities. Actually i was able to locate the line on which it is failing. But that line exactly same as any other line. Now I'm using a bcp to import the file into a table & then accessing the data from that table instead of file directly. Its working too...Is it a problem with DTS? Are there any patches for this?

Thanks for ur responses!
Ganesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources