SCD Dataflow Task Hangs during execution when Existing Dim is Empty

Mar 5, 2009 at 5:35 PM
Edited Mar 5, 2009 at 6:27 PM
I have:

150k records SRC table
43 SCD Type 1 columns
4 SCD Type 2 columns 



When executing the package while existing dim table is empty, the package will pull all the sorted SOURCE records into the dataflow, (the source objects turns green). however the SCD object will turn yellow and nothing will happen, i've waited for over an hour before killing it.

If I limit my SRC query to top 1000 on the SRC the package will complete in 5 seconds, i then can slowly step up amount of records by 1000 until i get all of them and the package will complete.

Not sure what addtional information is needed to better explain my issue so feel free to request. I can also send my package and db backup if needed.


Not sure if this helps but I've noticed if i turn on the logging in the task to write to the output window, i see the below lines written over and over and over... 

Information: 0x1 at Load Patrons, Kimball Method Slowly Changing Dimension: ED 0/0(comp), SS 1/18161 - 0 determined

Information: 0x1 at Load Patrons, Kimball Method Slowly Changing Dimension: 0 II, 0 UC, 18160 NW, 0 S1, 0 S2



please help.

Stephen



Coordinator
Mar 6, 2009 at 5:25 PM
Converted to Issue 1953 - conversing in email.
Jul 1, 2009 at 4:14 AM

Hi
I seem to have the same issue as b0wl3y - hard to see exactly what you guys did to fix the issue. The first time this happened to me I did the same by limiting the number of rows in my source the SCD eventually became "un-hung" and worked. I then increased up to all my rows and it worked.

The issue has crept up again with a different dim only this time that workaround is not working. It was an empty dim but I manually added data with no luck - I even added an existing row and for source did a top 1 so I knew the output would be unchanged but no luck - the scd turns yellow and hangs. No error messages are reported.

Could you walk me through enabling some debugging so I might be able to send you more useful info? I’m using 1.4.5 SQL 2005
Thanks
Dan

also - for my other 15 dim's your component works great and this has saved me a ton of time - thank you!

Jul 1, 2009 at 6:22 PM

Update

I seeded my existing dimension with 100 rows then ran the task and it worked with no problems - populated 2k rows in less than 2 seconds. Strange thing is that after it succeeded I went back truncated my existing dim table and re ran the package and this time it populated all rows as new with no issues. Not sure why but I do have a workaround for future first time runs of my ETL.

 

Coordinator
Jul 1, 2009 at 7:25 PM

Dan,

This issue is strongly related to the threading in the component.  There are several threads working on different parts of the processing, and there's one part here where they aren't seeming to interact well together. 

Stage 1: The first "thread" is the main thread started by SSIS - it's call to the ProcessInput method.  In that thread, I queue up the rows that come in into a set of "unmatched" queues (one for each input.

Stage 2: The second (set of) threads are the "matching" threads.  I spawn those at the start of processing.  They look at the queues populated by stage 1, trying to find matching business keys between those queues.  If they find a match between queues, they take those rows out of the "unmatched" queue and put them into a "matched" queue.

Stage 3: The third (set of) threads are the "processing" thread(s).  Again, those are spawned at the start of processing.  They keep an eye on the "matched" queue, waiting for stage 2 to populate it.  The processing thread(s) examine the rows and determine if and how the row differs between the inputs, to determine if it's an New/SCD1/2/... etc

The problem you have is in some of the more complicated load balancing I'm performing inside the component.  In this case, the stage 2 set of threads could consume 100% of CPU activity all by themselves, all the time, because they could constantly search through all of the "unmatched" queues again, and again, and again - whether or not there's any new records on them.  I try to detect times where they should pause themselves, because no new rows have been added to the "unmatched" queues since the last time they checked every row in them.  (If no new rows were added, and the threads have already tried to match things up, going over the list again won't find anything more to move to the matched queues, so any more work is a waste of cycles - and would be stealing cycles from stage 1 or 3.)  It looks like I'm having an issue there - which (with kmartin's help) I believe I've fixed in v1.5.

At this point, I think I'm fairly close (a couple weeks) from having an alpha ready of 1.5 - and I want to contrive a test like this for it.  However, as you've found, it's inherently unreproducible, because it has to do with multithreading and confluence of specific events in time.  I can say that it's never happened to me.

One thing you could do is disable threading.  I normally use that for debugging only (so I don't have to freeze and thaw threads in the debugger).  This will make the stages happen one after another, with a very clear handoff, all from one thread.  You should see that option on the debugging tab - but you may need to be an admin on your machine to see it (I think I put that restriction in there...)

Jul 1, 2009 at 8:22 PM

Thanks for the quick, detailed response

Ill give disable threading a try and i have my manual workaround as well. Let me know if you want me to assist with finding root cause - if i can enable some low level tracing and send you a log i'd be happy to do so.

Overall I am extreamly pleased with the componenet - it is very easy to use, very efficient and has saved me a ton of ETL development time. Keep up the excellent work and I look forward to 1.5.

Dan

 

Sep 13, 2011 at 11:36 AM
Edited Sep 13, 2011 at 11:36 AM

Same happend to me, but my dimension wasn't empty and it was during design time. It has 3 rows.

Unfortunately this component isn't working...