Component Hanging on Execute phase is beginning.

Topics: Issues 3: Executing the Component (Run-Time)
Jun 22, 2011 at 5:21 PM
I'm able to use the Dimension Merge SCD Component to succesfully bring in new records into a dimension table.  
However in subsequent tests to bring in the same records, the component does not fully pump the records out to the Unchanged output.  
I have 69,839 rows comparing against the same 69,839 rows in the dimension table, and the component is stuck at outputting only 69,629 rows to the Unchanged output.
The SSIS output log shows that it's stuck at "SSIS.Pipeline: Execute phase is beginning."
I'm not sure why it's not processing all the input records.
I am using Dimension Merge SCD 2008 x64.
Jun 22, 2011 at 5:59 PM

Not sure why this matters, but as soon as I remove pre-sorted values from my OLE DB Source (Advanced Editor > OLE DB Source Output > IsSorted = False), then the Dimension Merge SCD component is able to process all the input records.  Is it better to use the Sort component as opposed to presorting the business key in the OLE DB source component?

Jun 23, 2011 at 3:12 PM

The Sort component should really not be used in most all situations as it is a fully-blocking component - that is it cannot flush a buffer of rows through it when done processing just that buffer as it must wait until all data has been received and processed prior to releasing any output rows to be processed by other components.

Have you tested this by running from the command prompt using DTExec or are you running this from within BIDS?

Can you provide the schemas for both the sources going into the Dimension Merge SCD component along with an indication of which columns are the matching keys? What data types are the columns that the match is being performed upon?

What do you mean by "stuck at outputting only 69,629 rows to the Unchanged output? Does it produce an error or does it hang and never complete?

Sep 18, 2012 at 7:20 PM

I, too, am seeing issues with the component (version 1.6, x64). It tends to hang up after consuming all the Source and Dimension rows (~25,000 each), and has processed about 1/2 out to the Unchanged output. From there, it just hangs, stops, does no more work and appears stuck. I have not been able to trace down what makes it happen, or what needs to be done to it (after stopping the Debug) to make it run to competion again. 25,000 rows is really not that much. Both inputs are sorted properly and identically.

Any help or feedback is appreciated.

Sep 18, 2012 at 10:36 PM

@toddchitt, the only workaround I know of is to not sort at the Source.  The Dimension Merge SCD works for me when I have IsSorted set to False in my OLE DB Source, and when I do not use the Sort component.

Sep 19, 2012 at 2:12 PM

Thanks. I tried that and it seems to have solved the issue.

Aug 13, 2014 at 4:49 PM
I had a similar issue, but wasn't satisfied with the workaround. After digging around for a while, I found that my sorted business key, a variable character field that started with a number, was sorted differently on SSIS versus SQL: given the values 1200a and 11000a, the two would sort differently.

The issue wouldn't come up on the first load, since the Dimension Merge component had nothing to merge. Subsequent merges, though, would sometimes give this issue.

I changed my source query to pad the left side of the value with zeroes until a consistent width was reached, and then sorted that in the source. My package runs fine now.

For more information, see this post, and note Cleyton Alencar's response.

Changing my source query from
SELECT 
    ID + '|' + CAST(POS AS VARCHAR(4)) AS COMPOSITE_KEY,
    ...
FROM ... 
ORDER BY COMPOSITE_KEY
to
SELECT 
    RIGHT('0000000000' +ID, 10) + '|' + RIGHT('000' + CAST(POS AS VARCHAR(4)),4) AS COMPOSITE_KEY,
    ...
FROM ... 
ORDER BY COMPOSITE_KEY
fixed my issue.