August 18, 2008 at 8:58 am
Hello,
I'm new to SSIS being baptized by fire so to speak. Any help anyone could give me would be greatly appreciated.
I have a Dataflow task with an access source, a conversion task to convert the strings, a sort task and then SQL destination. I seem to lose the data and it fails at the point of writing to the SQL destination.
I've written out the errors but I can't seem to figure out which column(s) are causing it to fail. I've attached a screen shot of the progression and the error log output. If anyone can tell me how to further track this error I'd be grateful.
Thanks,
Sabrina
August 18, 2008 at 9:25 am
If the SQL Server you are using with the SQL Destination local to your workstation? The SQL Destination is a bulk utility that will only work on a local server.
August 18, 2008 at 9:38 am
It does not look like a SQL dest component that he is using. Judging by the errors and icon.
But there should be something else in the way off errors. Something that points towards the offending column. If you are sure there is nothing else in the error messages, create an empty copy of the table with no constraints and populate it. If it fails, add one column at a time until it dies.
Looking at other people who had the error 0xC020907B, it seems to related to invalid datatypes. incorrect dates are common.
Does you data flow work if you use a UNION ALL as a destination?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 18, 2008 at 10:30 am
Forgive me for responding to two posts in one.
Micheal, the SQL destination server is not local and unfortunately it would not surprise me if there were some constrictions on the server side.
That being said, I've been investigating as well for similar errors and like Crispin suggested I'm leaning towards the invalid data type...I just had no clue of how SSIS might give me a 'hint' as to which one.
If there isn't a 'built in' way to determine which column is giving me the error (via logging or etc) then I'll try the adding one column at a time and see if I can isolate.
Crispin, I'm not sure I understand your context for union all because I have one source recordset/table to one destination. Could you clarify?
Many thanks to both of you for taking the time to respond.
Sabrina
August 18, 2008 at 1:15 pm
If you set your dest to be table (not fast load) and redirect any errors, does that give a hint? Other wise the slow method of column by column 🙁
As for my union all suggestion, you can use a union all as an "unfussy consumer". But having a second look at your screen dump, the error was indeed at the dest and not somewhere else. (It was close to hometime...:) )
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 18, 2008 at 3:21 pm
Crispin,
Thanks for the response. Looks like it will be the slow column by column method because my error out is failing with the same error. I've identified a couple of columns and you were (as expected) right on with the fact that dates are proving to be 'ugly' contributors to my plight.
I'm guessing from your response about the hint that once I've identified the problem columns it's a trial and error thing to trouble shoot the problems with my conversions. I've come accross the unicode vs. ansi threads for string conversions but not anything on dates.
Your direction is humbly requested.
Sabrina
August 18, 2008 at 3:30 pm
If you suspect date columns, they are obviously dates which ssis does not mind but SQL does. I have had this in the past with dates of 1 Jan 1020 something. SSIS does not mind, SQL moaned.
In my opinion, the row of data in that case is invalid. Reject it.
You would have to have some sort of validation on the rows / dates. Script task is most flexible. Conditional split fastest. You could set SQL to redirect rows but that is penalising 99% of the data (speed issues) for 1% that is invalid.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply