how to deal with error messages in flat files to be imported
Problem: Flat files needing to be imported are sometimes coming with error messages included with the normal rows of data. The flat files were generated on SQL 2005 using SQLCMD. Here is an example of an unexpected error message I found in the first few lines of a flat file: Msg 8152, Level 16, State 13, Server VSQL003\SQL003, Line 1 String or binary data would be truncated. The statement has been terminated. Customer LiveDate ------------------ ------------- ABC Corp. 2011-01-15 First Int'l 2010-09-03 AdventureWorks 2009-04-28 Is there some way to route text lines that can't be processed into a table or new flat file for later examination? Most important is that the automated import procedure succeeds, which it won't with these "bad" text lines. Thanks, Eric
April 20th, 2011 6:47pm

Set the Data Access mode on the OLE Db Destination to "Table or view" (not 'fast load'), then drag and drop the red arrow eminating from it onto a new Flat File Destination. (I usually put in a Row Count between the two). Set the Flat File Destination properties. You will also need to set the Error Configuration on the OLE DB Destination to 'Redirect Rows'.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2011 9:31pm

Thanks, Todd C. Your explanation was a good one, but the error occurs at the flat file source, whereas you have taught me how to catch errors at the table destination...correct? I used what you taught me here to try the same on the flat file source. The 4 "bad" lines I included above were filtered out--to where I'm not sure because the flat file doesn't contain the "bad" lines of text--and the "good" text was imported successfully. I've got some figuring out to do here, but you've pointed me in the right direction yet again, Todd C. Thank you. Here's my flat file for flat file source errors, not at all what I expected: Flat File Source Error Output Column ErrorCode ErrorColumn ----------------------------------------------------------------------------------------------- -1071607676 462 I was hoping to be able to catch the file name, process date/time, error message, and entire line of text that could not be processed all in one line.
April 21st, 2011 1:22pm

I understand that in your FlatFile Source at least one column definition includes a length specification that is shorter than the data coming in. If you want to test for data quality issues then you need to skim through the file say using a Script Task Component in which you will able to check for data issues and remove or log those offending records to a table.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
April 21st, 2011 1:34pm

I usually open up the column widths of the Flat File Connector until I don't have issues, like up to 50 or 100 characters. Then use my suggestion above to pull out offending rows when you send them to your OLE DB Destination. Or, put in some Derived Column or Data Conversion Transforms to shorten the columns to what they should be and set the Error Configuraitons to "Ignore error"Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
April 21st, 2011 1:37pm

ArthurZ: Yes, my first column expects 128 characters and my second column expects a datetime, and since the first line of text shown above is only 57 characters, the error is: Data conversion failed. the data conversion for column "ExtractDateTime" returned status value 2 and status text "The value could not be converted because of a potential loss of data." In other "bad lines", the text goes on far more than 128 characters, which seems to get an identical error. Could you point me to a page that describes using the Script Task Component for this purpose? Thank you very much.
Free Windows Admin Tool Kit Click here and download it now
April 21st, 2011 2:26pm

You have two issues: Data types do not match because it seems the FF source picked the incorrect ones, and Truncation issues. To resolve #1 you need to right click and choose "Show Advanced Editor" in which you will need to adjust the data types. Regarding the truncations you wanted to exclude those and save them aside so yes, you can do it using a script transformation like there: http://www.bimonkey.com/2009/09/the-script-transformation-part-1-a-simple-transformation/ Instead of the transformation in the example above you - a) Check for record length, if < 128 then b) log it to a file like this Dim tw as TextWriter = new StreamWriter("myerrorfile.txt") tw.WriteLine(Row.MyOffendingRow.ToString()) tw.Close() PS: You may need to import the proper library (e.g. System.IO)Arthur My Blog
April 21st, 2011 2:54pm

I have one issue: junk in my text file. Said junk only seems to exist on lines 1 through n, and then my column headings begin on row (n+1). For example: Line 1: Some sort of SQL Server error line that was created by the source query, somehow, some way. Line 2: A shorter error line of some sort. Line 3: CustomerID CustomerName CustomerDOB Line 4: ---------------- ------------------- --------------------- Line 5: 345123 Joe Whatshisname 2011-01-01 08:15:23.224 My problem is not with data types or truncation issues, my problem is there are unexpected lines of text in the source file.
Free Windows Admin Tool Kit Click here and download it now
April 21st, 2011 5:00pm

Enter the SCRIPT COMPONENT on the Data Flow. You can design the script to read the flat file row by row and send data to the output pipeline ONLY if the row contains actual data. You would need some acid test to determine if the row was actual data, or was gibberish that could safely be discarded. For example, assuming that the first field is CustomerID and might contain a numeric value, then you could examine the first 6 character of each line. If it numeric, then consider it to be data, and parse out the rest of the fields based on spacing, comma, whatever. If not, go on to the next line.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
April 21st, 2011 9:15pm

This problem has been solved. I'm using a Script Component to get the ErrorDescription, then the error goes to a flat file. The error message is basically the result of trying to import a different error message, which would be of no use to me. To know there's an error in the file is to know there's a problem with the source query, and that's where I need to focus my efforts. As long as the automatic imports can execute without a hitch--even if several rows of data are missing--the boss is happy. Thank you, Todd C and ArthurZ.
Free Windows Admin Tool Kit Click here and download it now
April 22nd, 2011 11:03am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics