Problem using dts with large file

Question

Problem using dts with large file

François Desbiens

SSC Enthusiast

Points: 196
More actions
September 21, 2005 at 1:23 pm

#76424

I have a problem running my DTS package that read logs files and put them into one table.
The thing is, My log files have 50 000 lines. When im running the package, it fail. If I use the same exac data and I put the first 25 000 line in one log file, and the other 25000 line in another log file and I run my DTS package with both file separatly, its working.
Someone already had the same problem? Any clue why its not working with the 50 000 line files?
Cause my actual file im suppose to run with the DTS package are log file with more then 500 000 lines of data, I want to know why its not working and find a way to resolve that problem.
Thx a lot for your reply.
Francois

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Francois,

Please post the error message you're getting and the format of your source file, and which DTS task you're using to import the data.

Thanks,

Greg

François Desbiens SSC Enthusiast Points: 196 More actions · Answer 2

thx for replying.

The message that was in french that I translated: Excessive number of column in the current row. Characters other than space have been found after the last column of data define.

Format.

172.24.226.31 - RICM03.DIR_COM.Ste-Foy.MJQ [03/Jun/2005:09:04:55 -0400] "GET http://radio-canada.ca/nouvelles/ressou3/template/inc11.js HTTP/1.0" 200 6906

Space delimiter for the column. The only transformation done is to take out the first "[" in the date column. The date go in a nvarchar column in the destination table. The DTS task is a simple data transformation task. I take col1, col3, col4, col7, col10 from the source file, and put them in the table column.

The problem is, that it fail when I run the dts package for one log file that have 50 000 rows. And it work when I use the exact same data but that was split in two files of 25 000 rows.

Thx for your reply

Francois

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 3

Try to create a log and see which row is failing, it seems that there is one row that has missing or extra columns.

To configure a log file, right-click on the transformation task, go to Options tab, in the Exeption file put a name for the file. After this, run the task again and look in the log for the line number where it's failing.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

you have space within your time value.

So when you remove brackets your 1 column containing date becomes 2 columns.

And if problem is not in excessive number of columns but in size of the file try to use osql utility.

_____________
Code for TallyGenerator

Edward W. Stanley SSCertifiable Points: 6512 More actions · Answer 5

There are odbc processes for putting the data directly into SQL from your log files, you do not need to use DTS.

Then you can do copies and truncates, assuming the below statement does not occur.

I think you are just going to kill your SQL SERVER, but go nuts.

Put the horse in front of the cart.

peterus SSC Veteran Points: 287 More actions · Answer 6

I would think that by raising the allowable number of errors you could just skip these errant data and manually enter them if need be.