Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

SSIS Fuzzy Lookup Error

A Fuzzy Lookup Task in SSIS that I was working on was bombing out, and I had a heck of a time figuring out why. The error did not tell me much:

Error: 2011-05-18 10:42:09.33     Code: 0xC0047022     Source: Fuzzy Match InfoUSA SSIS.Pipeline     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component “Fuzzy Lookup” (49) failed with error code 0x8000FFFF while processing input “Fuzzy Lookup Input” (50). 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.  There may be error messages posted before this with more information about the failure.
End Error

Error: 2011-05-18 10:42:11.50     Code: 0xC02020C4     Source: Fuzzy Match InfoUSA CUSTOMER [1]     Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
End Error

Error: 2011-05-18 10:42:11.58     Code: 0xC0047038     Source: Fuzzy Match InfoUSA SSIS.Pipeline     Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component “CUSTOMER” (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.  There may be error messages posted before this with more information about the failure.
End Error

After a lot of digging, what I found out is that it was a server memory issue: The Fuzzy Lookup Task loads the entire reference table defined in the task into memory.  A large reference table could eat up all of the memory on the server.  That is what was happening to me.  The server had 8MB of memory, and I was loading in 2 million records.  Once all the free memory was gone, the task bombed.  When the task runs, you can jump on the server and watch how the memory slowly gets eaten up.  The only solution if you want to continue using the Fuzzy Lookup Task is to get more memory, free up memory, or reduce the number of rows in the reference table.

Also note that if the job does not bomb but there is very little free memory, the Fuzzy Lookup Task will perform very slowly.  When I reduced the number of rows in the reference table to 1.8 million, the task did not bomb, but took over an hour as the free memory shrunk to about 3%.  When I reduced the number of rows to 1.5 million, it left 10% free memory and the task took only 10 minutes.

Comments

Posted by Anonymous on 14 June 2011

Pingback from  Dew Drop – June 14, 2011 | Alvin Ashcraft's Morning Dew

Posted by Anonymous on 14 June 2011

Pingback from  SSIS Fuzzy Lookup Error – James Serra's Blog – SQLServerCentral.com | Error code 61

Leave a Comment

Please register or log in to leave a comment.