Odd Behavior on New Table

Nov 16, 2009 at 5:07 PM

I apologize if someone has posted this question before but I'm seeing an odd behavior with a new table on the new output.  When I run through and process the source, all records are correctly routed to the new output as New Records.  If I immediately execute the package a second time with the exact same source, all records are added a second time; however, if I execute a 3rd time, the records correctly go to the unchanged output.  I've reproduced this 5 times with the exact same behavior. 

If I write a query to do a left outer join after the first run, the query does not find any new records.  By the way, my business key is an integer. 

I'm not convinced this is an issue with the KSCD component but was curious to see if anyone had any thoughts on this or experienced the same problem.


Nov 16, 2009 at 8:33 PM

That does sound extremely odd - and the only thing I can think of is that your "existing dimension" flow isn't picking up the rows that were added in the first run.  This should be very easy to check, though.  On your second run, have the component write row input and output counts to variables, then store those variables somewhere (text, SQL db) that you can get at them.  Or you can route the Auditing output to a file or table to look at the row counts.

You should be able to see if the Existing Dimension input is receiving any rows on the second run, and also see what the output row counts look like.  Post back with that information.

Nov 17, 2009 at 5:56 PM

Quick update -

Todd, thanks for the suggestions on tracking this down. I did as you suggested and dumped the counts to variables and it appears that it is reading in the records that were loaded in the first load. I’m loading a small set (1766 records) and on the second run, I get 1766 for the dimension, the source, and new output. I also thought the issue could be with the session not closing and therefore not committing the changes but I’ve ruled that out as well. To add some additional data to the mystery, I can go in and delete the first set of records after the duplicates are loaded (leaving only the second set of records that were added to the table) and it will not recreate the duplicate issue a second time. It appears to only happen after the initial load of the table.

I’m going to continue to dig into this to try and determine the cause just in case anyone else runs into the same issue but my expectations are pretty high that this is something boneheaded that I’ve done…

Nov 18, 2009 at 6:02 AM

Please do post back if you find something that could possibly be related to the component.  Even if you find it's a "boneheaded" thing that you did - we're all boneheads, and it's quite likely someone else will run into the same obstacle.  If that possible misconfiguration can be designed out of the component, it's worth it to try.