I have two columns in my source flat file, an integer column and a varchar.
In the Data Flow Task, I selected the Source Object,and in the Configure Errors section, I set both the "Error" an "Truncation" columns to "Redirect" to an output file intended for rejected records.
If I rig the data with one row that has a varchar value that is longer that the length of the column, the row is redirected as expected to the Rejected flat file. However, when a string value is encountered in the INT column (ProductID), the package dies on the following error.
[OLEDEST ] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLEDEST ] Error: There was an error with input column "ProductID" (1084) on input "OLE DB Destination Input" (213). The column status returned was: "The value could not be converted because of a potential loss of data.".
At first I thought I need to change the task to force a success return return result of increase the max error count even though I thought that "handled" errors should really be considered as errors. However, this only ensured that the package completed successfully, the row with the varchar value for ProductId still didn't end up in the error file.
What am I missing?
Yes, it looks like I can change the definition of the file layout to include only string data types and that the Redirect Truncation path will work on the File Source object. To catch Conversion errors however, I think I need to use the Data Conversion transformation and redirect rows from that Task.
For numeric data types, I would define the columns in the Flat File Source object "sufficiently large" to handle any sane value, then leave it to subsequenct Data Conversion task to handle type conversion errors.
This sounds like what Preet is suggesting...
Update 2 When I create a simple file with 2 columns, int and varchar and then put a char value in the int column and run a test package that redirects rows from the Flat File Source object, nopn INT columns ARE redirected. Not sure why this isn't happening in my other pkg which I am originally questioning. Maybe it is related to the fact that this package was GENERATE using .NET code and something is screwed up.
Summary question for this post:
If I redirect rows from a Flat File Source, should rows containing integer columns with non int values be redirected to a flat file or would you expect this to abort the package? (as described above, I am getting inconsistent results and don't know why)