Error while debugging the Step 9: Testing the Lesson 1 Tutorial Package
Hi all, I am following the Step 9: Testing the Lesson 1 Tutorial Package and have run into an error at the Lookup Date Key my error message is as follows: SSIS package "Lesson 1.dtsx" starting. Information: 0x4004300A at Extract Sample Currency Data, SSIS.Pipeline: Validation phase is beginning. Information: 0x4004300A at Extract Sample Currency Data, SSIS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Extract Sample Currency Data, SSIS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Extract Sample Currency Data, SSIS.Pipeline: Pre-Execute phase is beginning. Information: 0x402090DC at Extract Sample Currency Data, Extract Sample Currency Data [1]: The processing of file "C:\Users\bishal.TRIANGLE\Desktop\sample adventworks\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" has started. Information: 0x400490F4 at Extract Sample Currency Data, Lookup Currency Key [26]: component "Lookup Currency Key" (26) has cached 14 rows. Information: 0x400490F5 at Extract Sample Currency Data, Lookup Currency Key [26]: component "Lookup Currency Key" (26) has cached a total of 14 rows. Information: 0x402090E2 at Extract Sample Currency Data, Lookup Currency Key [26]: The component "Lookup Currency Key" (26) processed 14 rows in the cache. The processing time was 0.001 seconds. The cache used 616 bytes of memory. Information: 0x402090E4 at Extract Sample Currency Data, Lookup Date Key [51]: The component "Lookup Date Key" (51) succeeded in preparing the cache. The preparation time was 0.001 seconds. Information: 0x4004300C at Extract Sample Currency Data, SSIS.Pipeline: Execute phase is beginning. Information: 0x402090DE at Extract Sample Currency Data, Extract Sample Currency Data [1]: The total number of data rows processed for file "C:\Users\bishal.TRIANGLE\Desktop\sample adventworks\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" is 1097. Error: 0xC020901E at Extract Sample Currency Data, Lookup Date Key [51]: Row yielded no match during lookup. Error: 0xC0209029 at Extract Sample Currency Data, Lookup Date Key [51]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Lookup Date Key" (51)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Match Output" (53)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Error: 0xC0047022 at Extract Sample Currency Data, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Lookup Date Key" (51) failed with error code 0xC0209029 while processing input "Lookup Input" (52). 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. Information: 0x40043008 at Extract Sample Currency Data, SSIS.Pipeline: Post Execute phase is beginning. Information: 0x402090DD at Extract Sample Currency Data, Extract Sample Currency Data [1]: The processing of file "C:\Users\bishal.TRIANGLE\Desktop\sample adventworks\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" has ended. Information: 0x40209314 at Extract Sample Currency Data, Lookup Date Key [51]: The component "Lookup Date Key" (51) has performed the following operations: processed 1 rows, issued 1 database commands to the reference database, and performed 0 lookups using partial cache. Information: 0x402090DF at Extract Sample Currency Data, Sample OLE DB Destination [76]: The final commit for the data insertion in "component "Sample OLE DB Destination" (76)" has started. Information: 0x402090E0 at Extract Sample Currency Data, Sample OLE DB Destination [76]: The final commit for the data insertion in "component "Sample OLE DB Destination" (76)" has ended. Information: 0x4004300B at Extract Sample Currency Data, SSIS.Pipeline: "component "Sample OLE DB Destination" (76)" wrote 0 rows. Information: 0x40043009 at Extract Sample Currency Data, SSIS.Pipeline: Cleanup phase is beginning. Task failed: Extract Sample Currency Data Warning: 0x80019002 at Lesson 1: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) 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 "Lesson 1.dtsx" finished: Failure. Any help would be greatly appreciated, Thanks in advance.
September 5th, 2012 8:40am

It would be useful if you included the actual link to the tutorial. This is the culprit: Row yielded no match during lookup. This probably means you configured the Lookup component to error out when no match is found. Reconfigure your lookup to route unmatched rows to the no match output or to ignore failures.MCTS, MCITP - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2012 8:44am

I am actually following the sample adventworks located in this link http://msdn.microsoft.com/en-us/library/ms170651(v=sql.105)#CommunityContent I actually have followed the steps till this lesson but with one exception. I mapped one of the input column CurrencyAlternateKey to the CurrencyKey apart from that I think the other mappings are correct, they are as follows: TimeKey, AverageRate, EndOfDayRate 
September 5th, 2012 8:51am

They don't mention it in the tutorial, but you need to configure the lookup component on what to do with rows where the lookup failed.MCTS, MCITP - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2012 9:25am

Thanks for the reply Koen but to tell you the truth I am a newbie could you please shed some light on which column I should look up to, I have tried with CurrencyId as well but it has failed.
September 5th, 2012 9:30am

It's not a problem with the columns, it is a configuration issue. Read the section "Specify how to handle rows with no matching entries" http://msdn.microsoft.com/en-us/library/bb895370MCTS, MCITP - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2012 3:36pm

seems like you have configured the lookups in a wrong way. please follow the step 6 again, and check whether you have something left out. http://msdn.microsoft.com/en-us/library/ms167106(v=sql.105)Regards, Chirag Patel (ETL Engineer)
September 5th, 2012 9:05pm

I have double / triple checked it but still to no avail
Free Windows Admin Tool Kit Click here and download it now
September 6th, 2012 4:06am

I have double / triple checked it but still to no avail Go to this article: http://www.sql-server-performance.com/2009/SSIS-New-Features-in-SQL-Server-2008-Part2/ Look at the second screenshot. It's the General Pane of the Lookup transformation editor. There's a line that says: "Specify how to handle rows with no matching entries". What have you configured there?MCTS, MCITP - Please mark posts as answered where appropriate.
September 6th, 2012 4:21am

Thank you very much Koen Verbeeck I think the answer lies in the link you provided, I did tweak with the error output listbox and is displaying green now but with an exclamation sign, I think if i follow the diagram to handle the errors I should be fine. Again many thanks
Free Windows Admin Tool Kit Click here and download it now
September 6th, 2012 4:34am

Ran across this post as I had similiar issues. It appears the DimDate table has been updated to start in 2005 and the SampleCurrencyData.txt file has dates from 2001-2004 so you will get zero matches when doing a lookup. Not sure if there is a newer rev for the samples.... Kelly
October 1st, 2012 3:36pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics