August 23, 2011 at 8:35 pm
I am reading in 6000+ records in a complex query. Then Merge/Joining a couple of columns to the dataflow. Finally I am attempting to write all of the records to a table on our SQL Server 2008 using an OLE DB Destination module. For reasons that I don't understand only 4000 records are INSERTing into the empty table. The remaining 2000 rows are failing.
I redirected the failed rows to a CSV flat file. So far I can find no reason for the 2000 rows to fail. I removed all keys and constraints from the table.
Does anyone know how I can determine the reason why the rows are not inserting? Is there a log that I can examine?
August 23, 2011 at 10:52 pm
Typical failure reasons are data truncation or data type mismatch in the fields. make sure the fields line up properly for those that are failing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2011 at 3:53 pm
If you put a script component between the destination and your error output CSV you can get the description of the error. Here's an article[/url] that talks about this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 24, 2011 at 5:54 pm
I figured it out today. First I tried turning on SQL Profiler but with an OLE DB connection it saw no errors. I am guessing here but it looks as though the OLE DB driver was capturing/anticipating the error and failing the record.
Next I swapped out the OLE DB data destination for a SQL destination. It failed and SQL Profiler posted an error converting Date formats. I looked over the formats of the incoming and destination columns - they were all the same.
Finally I turned off the 3 date fields in the SQL destination bulk writer and it wrote every record.
Two of the fields are guaranteed to have a a date but one can be NULL. It seems that when a NULL date comes out of an Oracle database, SSIS interprets it as 1/1/0001. This is outside of the range of a smalldatetime. I put a derived column transform in the flow that looks for dates before 1/1/1800 and Nulls the field.
Wow - what an tough debug this was.
Anyone have any idea why SSIS sees a Null Oracle date as 1/1/0001??
August 24, 2011 at 6:23 pm
I think that is because .NET is taking the null which is ASCII 0, I believe, which when converted to a .NET DateTime is 1/1/0001.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply