Component Hanging on Pre-Execute phase

Topics: Issues 3: Executing the Component (Run-Time)
Nov 25, 2010 at 9:16 PM

I have created dimension build using this component.   The initial run ran successfully and correctly populated an empty dimension table with 223k rows.   I then re-ran the component with no changes to the source (so 223k rows, which will be compared with 223k in the dimension table) and it starts to run but then hangs on the pre-execute phase at 58%.    Has anyone seeen this before?

Also, I put an explicit order by clause on the sql statements for the source and for the existing dimension and I still get the optimization warning "Performance is not optimized - Existing Dimension identifies a Business Key, but the input is not sorted.  Sorting the input according to the Business Key can increase runtime performance."   I am missing something here?

Nov 25, 2010 at 9:21 PM

Here are details for the debug log:

Information: 0x4004300A at Process Dim_Person, DTS.Pipeline: Validation phase is beginning.
Warning: 0x0 at Process Dim_Person, Kimball Method Slowly Changing Dimension: Performance is not optimized - Existing Dimension identifies a Business Key, but the input is not sorted.  Sorting the input according to the Business Key can increase runtime performance.
Warning: 0x0 at Process Dim_Person, Kimball Method Slowly Changing Dimension: Performance is not optimized - Surrogate key specification depends on caching and scanning all input tables to find an appropriate value for new dimension keys.  Consider pre-calculating the 'next' surrogate key, storing it in a variable, and identifying that variable in the Surrogate Key settings on the Options tab.  Doing so can increase runtime perfomance.
Warning: 0x80047076 at Process Dim_Person, DTS.Pipeline: The output column "PersonKey" (9001) on output "Union All Output 1" (8997) and component "Union All" (8995) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at Process Dim_Person, DTS.Pipeline: The output column "RowChangeReason" (221) on output "OLE DB Source Output" (152) and component "DimPerson" (142) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at Process Dim_Person, DTS.Pipeline: The output column "AuditKey" (224) on output "OLE DB Source Output" (152) and component "DimPerson" (142) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x40043006 at Process Dim_Person, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Process Dim_Person, DTS.Pipeline: Pre-Execute phase is beginning.

Nov 26, 2010 at 3:29 AM

Figured out the sorting thing but setting the sort properties on the advanced settings of the source output.    I also got rid of the warning related to the surrogate key specfication by changing a setting on the Surrogate Key tab of the SCD transform.   We are using an identity column, so I let that manage it.

Still gettting the issue with the job hanging at 58% on the pre-execute phase.

 

Coordinator
Nov 27, 2010 at 12:14 AM

I'm not sure why it would be hanging in the PreExecute phase.  At that point, it's merely collecting information from the data flow - no rows have been read by your sources yet, much less the component itself.

What version of the component are you using, SQL and OS versions, and bitnesses?

Jan 17, 2011 at 1:56 AM

I can confirm this behavior. When you first drop the component SCD2 date handling 'variable containing "now" value' is blank. Everytime I try to run the component while this value is blank it will fail in the pre-execute phase. When I select a variable from the drop down the component runs successfully. This was observed on version 1.6 Alpha, Server 2008 x64 (SQL and OS)

Coordinator
Apr 8, 2011 at 8:00 PM

That's VERY odd.

If the "now" variable is blank, or set to a variable that doesn't exist, or set to a non-DateTime typed variable, then the component should report itself "broken"... and shouldn't execute at all.  If you could upgrade to the v1.6 release (thanks for letting me know you tried alpha) and confirm that, I'd appreciate it.