I’m importing a Flat file (delimited text) into a sql server database table and trying to trap any import errors in another output Flat file.
I create a Flat File Source task. After tweaking the task, the Source “Error Output” tab shows all the input columns and all “Error” and “Truncation” values are set to “Redirect row”
Next I add a “SQL Server Destination” task and connect the Source green arrow to it.
Finally I add a Flat File Destination task (error Output task), connect the Source red arrow to it, click “OK” in the “Configure Error Output”, and finally add a connection manager to the Error output task. When I look at the Mappings tab of the Error Output file it shows only three available input columns: “Flat File Error Output Column”, ErrorCode, and ErrorColumn
I’m not sure where they came from but that info is not very useful to me. I want to know which line the error occurred on as well as the bad column(s). If nothing else, I need to see at least the actual row that was bad. How can I get that information
Barkingdog
SSIS Error Output -- how is it supposed to work?
blogg
There is some information here that you may find helpful: http://msdn2.microsoft.com/en-us/library/ms141679.aspx
The data that is in error is passed to the Flat File Error Output Column - it is a DT_TEXT column because we have no way of knowing what the maximum size of your error data may be. A badly formatted flat file with missing delimiters could perhaps stream a great deal of data to the error output.
You can read the DT_TEXT column in the text file that you send it to.
Donald
ctwalker
Just to expand on Donald's last point...here is how you do that: http://www.sqlis.com/default.aspx 37
-Jamie
treble1999
The problem is that in flat files the relationship between line numbers and rows only works for certain cases - in some cases, such as fixed width, or where row delimiters are symbols, there may be no "lines" in the flat file at all.
If we do have lines in the flat file (where good old CRLF is the row delimiter) and we come across an error, we could perhaps send a line number to the error output. However, that works only for the first error. If the first error actually causes a problem that extends across several lines, we would not know where to pick up the line numbering thereafter.
Given the old parsing of DTS - looking for row delimiters first - we could possibly do something similar. But now we would be adding even more conditional logic to the parsing of every row, with consequent loss of performance.
Truth is, handling all the possible variations of even simple flat file formats, and the possible errors that can occur, is a performance killer. My preference is to have a super-fast flat file reader that can handle well-formed common formats as efficiently as possible, with some error handling - alternatives for less robust formats could be provided as a slower option.
It's probably too much of a stretch to have one component that can load both the terabytes of flat file data we see in high-end ETL, and the gnarly text files we see in some cases.
In the future, if we can provide more template script components, I could see enabling much more subtle text file handling in that way, or through regular expressions. In fact, for gnarly text files I typically read them in as one column and then parse them using derived column or regular expressions using script or the sample RegEx component: http://www.microsoft.com/downloads/details.aspx familyid=c16f11ad-150a-4091-b3a2-83d21d3e0973&displaylang=en
One reason I prefer to read in difficult formats as one column per row, is that I can then catch column by column errors when I am parsing them out downstream - and I can add numbering to the incoming rows too, using a simple script.
Donald
dalek9
jhazucha
That msdn2 article was interesting but I'm still puzzled.
Is there no practical way for me to tell which line(s) (I've given up on the column, just get me the line numbers) in a text file an import\export error occurred on
If not, then "practical" error handling in SSIS does not seem to have advanced much over that offered by sql 2000.
Or am I simply taking the totally wrong approach to capture these errors Is there a better way
TIA,
Barkingdog