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.
| Author |
Topic |
|
DravenX
Starting Member
5 Posts |
 Posted - 2002-06-21 : 12:19:43
|
| I am trying to import a very large text file(100,000+ lines) into a database table using a Text Connection in my DTS Package. However, each time the data transformation hits 68245 lines, it stops no matter how large the file is. Has anyone seen this situation before? If so, is it fixable and how? Thanks much. |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-21 : 12:29:46
|
| First bit for ya, 100k lines for DTS is small for what it can do.Second, check for triggers, foriegn keys, and other constraints on the table your loading here. Views as well... It will also do any indexing on the table that you have setup at this time.thrid, it may be a problem in the text file itself. Since it's picking a number like 68245 and not 68000 it probably means it's encountered what it thinks is the end of the file. When you say freeze, do you mean for about 10 mins? or a couple hours? Often it seems to freeze, but it's actually going over the index, checking keys and constraints/triggers and stuff like that. So I guess remove all constraints on the table and run it. Post up the results... I'm constantly running DTS programs and am curious why it'd freeze like that.-----------------------Take my advice, I dare ya |
 |
|
|
DravenX
Starting Member
5 Posts |
Posted - 2002-06-21 : 12:32:50
|
| It has never frozen. It just assumes that the file is loaded and the package finishes executing. I have tried several large files with the exact same result each time. Should I perform a cleanup on the files to insure that the correct EOL and EOF markers are in place? |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-21 : 12:37:36
|
| Hmm, sorry.. read your question a bit wrong. As far as size goes 68k lines is no upper limit.... I'm actually currently running one that is 4 million. I'm thinking it's something wrong with your text files then. An EOF seems to be appearing at the same line for all your txt files. 2 questions : Whats creating the txt files? I think the problem is thereWhat SQL version you running there?-----------------------Take my advice, I dare ya |
|
|
|
DravenX
Starting Member
5 Posts |
Posted - 2002-06-21 : 13:10:05
|
| Text files are coming from an AS/400 system. I am running SQL 2000 with latest sp's. I am about to attempt running a file cleanup program on the master text file to see what happens. Will let you know. |
|
|
|
DravenX
Starting Member
5 Posts |
Posted - 2002-06-21 : 13:58:39
|
| No such luck. There are no table constraints and just to be sure, I maxed out the varchars to 255. Still did not affect it in any way.The upload hits 68245 records and thinks that it is finished. |
|
|
|
DravenX
Starting Member
5 Posts |
Posted - 2002-06-21 : 14:22:54
|
| Found the problem. Turns out that I had not changed the default setting of using double quotes as a text identifier and the previous line had a single double quote at the end. |
|
|
|
|
|
|
|
|
Text Connection Import with DTS