I just watch a very nice diagram of SSIS doing its work, and throwing 70% of the input rows into the garbage. I was a bit concerned about that, as you may imagine. I talked before about the experiance of debugging a problem with SSIS, I present this case as a case study.
First, I should mention that I pipe all the errors to a table that contains three columns (which are provided by SSIS). The columns are ErrorColumn, ErrorCode and ErrorOutput. So when I get an error, I just check the error table and see where they happen. In this case, I got the following results (times 70 thousands):
ErrorCode ErrorColumn ErrorOutput
-1073442796 162 null
"Hm, that is strange", I think, and goes to check the SSIS package for the column. I get to the point of the failure, and then I need to use the advance editor to go through each and every one of the dozens of columns that I have there, searching for a matching LineageId.
I have no idea what a LineageId is, by the way. I can't really think of why I would want to use that as my error column identifier rather than the rather nice column name.
Leaving that aside, I'm left with the entirely too opaque error code. Searching Google for this gives nothing, nor does search MSDN. So I'm basically getting told: "I know what the error was, but I am not going to tell you what it was."
After checking that the connection manager was fine, I noticed that it was converting a string to an int, so I tried trimming the string before converting, but it wasn't that, I kept getting the same error on the same column.
I outputted the offensive column as text to the table, and looked at the values (it sounds easy to do, but it takes over 5 minutes to do this, and adjust everything that it will work correctly).
All the values were perfect numbers, and SQL Server had no problem to convert them to numbers.
This is usually the point where I resort to physical violance and hit my head against the wall until I either find the answer or pass out.
After picking myself from the floor and passing the "Who I am? Where am I? What is this big bulge on my head?" stage, I had tried looking over the raw data. That was fine, but I noticed that several numbers were quite a bit bigger than the others. Then I checked the type of the column in the data base numeric(20,0) and the type I was converting to numeric(3,0), and it was obvious what the problem was.
Now, if I could get something like "truncation would occur", that would have saved me several hours of head butting and severe annoyance.