I continue to receive
Error: 0xC0047022 at Data Flow Task, Fuzzy Grouping Inner Data Flow : DTS.Pipeline: The ProcessInput method on component "Fuzzy Lookup" (97) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Data Flow Task, Fuzzy Grouping Inner Data Flow : DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x8007000E.
Error: 0xC02020C4 at Data Flow Task, Fuzzy Grouping Inner Data Flow : OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047038 at Data Flow Task, Fuzzy Grouping Inner Data Flow : DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047039 at Data Flow Task, Fuzzy Grouping Inner Data Flow : DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, Fuzzy Grouping Inner Data Flow : DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047021 at Data Flow Task, Fuzzy Grouping Inner Data Flow : DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC020828A at Data Flow Task, Fuzzy Grouping [130]: A Fuzzy Grouping transformation pipeline error occurred and returned error code 0x8000FFFF: "An unexpected error occurred.".
Information: 0x402090DF at Data Flow Task, Matches [875]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, Matches [875]: The final commit for the data insertion has ended.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Matches" (875)" wrote 1572785 rows.
Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
I have set the maximumerrorcount =10000 on the data flow task and still get the same error. Server is a dual 3.6ghz with 3.5gb ram AWE is off
Any ideas?Brian,
Increasing the value of MaximumErrorCount will not cause the error to go away. If you set MaximumErrorCount on package (not data flow task) to any integer larger than 8, package will finish successfully despite of the error.
That is, instead of
Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
It will return:
SSIS package "Package.dtsx" finished: Success.
The error in Fuzzy Lookup is still there. Can you shed more details about your package? How is Fuzzy Lookup transform configured? It will be very helpful if you can share your package.
|||Its actually a fuzzy grouping, not a lookup. I tried to put the XML for the project in this reply, but it is too big, any ideas on how I can send you the package?
Its pretty simple: I have a OLE DB Source with 7 fields selected, ID, First Name, Last Name, Mother Maiden, Birthdate, Gender and SSN.
I pipe that to the fuzzy grouping where all but the ID are using fuzzy logic. ID is just a pass through. I have a minimum match of .65.
I pipe the results back to the OLEDB datasource that is a new table.
Pretty straight forward, with a couple weights on gender, dob, ssn and last name.
This worked with 45K records, but so far has not worked with 1.5 Million.
I turned on AWE and re-ran which also failed - same error
This package usually runs for 7 hours before failing.|||The current implementation of FuzzyGrouping invokes FuzzyLookup. From the error you post, it seems FuzzyLookup is causing trouble.
Please send the package to Runying.Mao at microsoft dot com
Could you please send some sample data besides the package?|||I'm having a similar problem. I'm trying to achieve a Fuzzy Lookup with a source table of 650000 records (people) and a reference table of 24 million records (on two fields: social security number and complete name). When I use about one million records rather than 24 million in the reference table everything works fine. Similarly when I reduce the rows in the source table and maintain size of the reference table everything works ok (but I still get in all cases warnings about low virtual memory).
Here's my output (when trying the full job):
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Warning: 0x8000FFFF at Data Flow Task, Fuzzy Lookup [15]: Catastrophic failure
Warning: 0x8000FFFF at Data Flow Task, Fuzzy Lookup [15]: Catastrophic failure
Warning: 0x800470E9 at Data Flow Task, DTS.Pipeline: A call to the ProcessInput method for input 16 on component "Fuzzy Lookup" (15) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Fuzzy Lookup" (15) failed with error code 0x8000FFFF. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC02020C4 at Data Flow Task, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x8000FFFF.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Data Flow Task, OLE DB Destination [35]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, OLE DB Destination [35]: The final commit for the data insertion has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (35)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
Please any help will be appreciated.
Regards and thanks.
GV
german @. NO amepla . SPAM com|||
Runying has been able to narrow down this problem and identified the issue.
A possible workaround is to ensure that the BufferTempStoragePath property of your data flow task is set to a drive with large enough empty space.
Thanks
Donald
Information: 0x4004800C at Data Flow Task, DTS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 6372 buffers were considered and 6372 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
It is clear to me now that when the reference table (for fuzzy lookup) is huge the package needs a lot of memory for building the fuzzy index. If you are using the same reference table all the time and the data there is rather static you should consider saving the index for future use which will save you a lot of time when running the package again. In my case a fuzzy lookup with a source table of 650000 rows and a reference table of 24 million rows took 72 hours and had to be done in 3 steps of aprox. 220000 source records otherwise the package always failed.
(P4 HT 2.6Ghz, 2Gb RAM, 160Gb SATA HD and lots of coffee =)
Regards,
GV
No comments:
Post a Comment