September 11, 2003 at 1:04 pm
Tried to DTS a text file into SQL Server and received following error messages.
"Error at Source for Row number 160886. Errors encountered so far in this task: 1.
Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Error Help File:DTSFFile.hlp
Error Help Context ID:0"
If I cut and paste this row (160886) to the first row in the text file, The DTS package works fine. But in real world, the row like this could appear in any number of rows in the text file. Anyone has ideas and solutions?
September 12, 2003 at 3:51 am
Before finding a solution you need to find what the problem was. Is it possible that the row in question had additional non printable character(s) or extra LF which was removed when you moved the data. Need to use a hex viewer to view the original file to check for extra data. Are you transferring fixed or variable data?
Far away is close at hand in the images of elsewhere.
Anon.
September 12, 2003 at 7:24 am
Most of records in the text file have same number of columns. But sometime, there are few exceptional records which have more columns.
September 12, 2003 at 7:33 am
Are you transferring the data as fixed or delimited?
Far away is close at hand in the images of elsewhere.
Anon.
September 12, 2003 at 7:44 am
It is delimited by ",".
September 12, 2003 at 8:11 am
I cannot reproduce the error and without the table definition,dts and data it is difficult to see what the problem is. If the data may contains extra columns you could add extra columns to the table and dts and then ignore them. Doubtful if this will cure your problem but it might help towards it.
Sorry I can't be of any more help.
Far away is close at hand in the images of elsewhere.
Anon.
September 12, 2003 at 8:42 am
Thanks for the help, Anyway. The problem is we really have no control how many columns the records may have. It is generated when we dump the event security log into the text file.
September 12, 2003 at 10:42 am
If that is the windows event log then I see your problem. The only thing I can suggest is to preprocess the file with an application or ActiveX script and then dts the preprocessed file.
Far away is close at hand in the images of elsewhere.
Anon.
September 12, 2003 at 12:02 pm
Yes, It is Windows security log. Thanks again.
September 15, 2003 at 4:04 am
I think your problems occur from the structure of the event data and it being on multiple lines. The following script which you can use to preprocess the comma separated data into single line entries (removing CR/LF) and use the output as input to your DTS. Test worked OK on my data.
Function Main()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso
Dim fin
Dim fout
Dim Buffer
Dim Buffer2
Dim Pos
Dim ct
Set fso = CreateObject("Scripting.FileSystemObject")
Set fin = fso.OpenTextFile("inputfile", ForReading)
Set fout = fso.OpenTextFile("outputfile", ForWriting, True)
Buffer = fin.ReadLine
Do While fin.AtEndOfStream <> True
Buffer2 = fin.ReadLine
If Len(Buffer2) > 10 Then
If IsDate(Left(Buffer2,10)) Then
fout.WriteLine(Buffer)
Buffer = Buffer2
Else
Buffer = Buffer & " " & LTrim(Buffer2)
End If
Else
Buffer = Buffer & " " & LTrim(Buffer2)
End If
Loop
fout.WriteLine(Buffer)
fin.Close
fout.Close
Set fso = Nothing
Main = DTSTaskExecResult_Success
End Function
Far away is close at hand in the images of elsewhere.
Anon.
September 29, 2006 at 8:18 am
I ran into this problem many times. It has to do with the export of the data, not how you import, unless you can check the number column prior to importing. Some how SQL Server will put an extra column, within the file. I had to just request a good batch of data. I don't know how it can be prevented.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply