November 25, 2008 at 7:59 am
Hi folks,
Playing around with a package that is bringing data in from a flat file (csv format) and after various transformations dropping the data into a SQL 2005 table.
I am redirecting the error rows to a text file. Of course this just gives us the original data (so useful...not), an error code that I can't find anywhere, and the column id.
I found a script to extract the ErrorDescription, but the row.errordescription does not seem to exist so maybe this code was for SQL 2008?
Anyway I just need to know why my export is failing - but I'm not having much luck. This must be something obvious - surely error tracking isn't that hard in SSIS! (I'm a recent DTS convert).
Cheers,
Shark
November 27, 2008 at 10:01 am
Ah you know SSIS is disappointing me somewhat. At first I thought it was great, but now I'm starting to get annoyed with it.
I was trying to import some migrated 6.5 data into a 2005 db as a test, but this simple task has proven so tough I'm not gonna use the quick route using ms access. Terrible!
The big downer for me is rows being rejected and it not telling you what the hell the reason is. Having to set an "error output" for every single step. In DTS a package errors, you double click on the error and get a message. In this its like a treasure hunt riddle. Whats that all about?
I'm doing a simple Data Flow - Flat File through 5 lookups and a derived column step into a table - and its just pants.
Somebody shine me a light!
November 27, 2008 at 2:57 pm
You mention that you have 5 lookups in your package... Are these the only transformations you have between your source and destination?
If you are getting an error in a lookup transform, the likeliest cause is that the value in the data flow is not found in the lookup table. The default behavior for a lookup transform is to fail the component if there are any rows - even one - that are not found in the lookup table.
Also keep in mind that the lookup transform is case sensitive even if your database collation is not. It's a little unsettling, but you have to remember to compensate for case differences if you are comparing character data.
You should also look in Execution Results tab (it will show up as the Progress tab while you are running the package). The error messages are a little cryptic but it can help you to track down the reason for failure.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply