I am trying to have lookup component to find some column is equivalent to max number of date from TR table. My query is below

select CAST(max(prDate)as datetime) as date from TrTable

In SSMS it returns value as 2018-05-17 00:00:00.000 while when I try same query in lookup connection it return result as 5/17/2018 12:00:00 AM.

Any idea how should I rectify lookup query to get exact same result as 2018-05-17 00:00:00.000 so that I would have a match and lookup will yield match output.

Lookup tasks produces following error -

[Lookup [114]] Error: Row yielded no match during lookup.

[Lookup [114]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Lookup" failed because error code 0xC020901E occurred, and the error row disposition on "Lookup.Outputs[Lookup Match Output]" 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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Lookup" (114) failed with error code 0xC0209029 while processing input "Lookup Input" (128). 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.

  • 1
    Those values are equivalent, just formatted differently. select a=cast('2018-05-17 00:00:00.000' as datetime) ,b=cast('5/17/2018 12:00:00 AM' as datetime) ,iif(cast('5/17/2018 12:00:00 AM' as datetime)=cast('2018-05-17 00:00:00.000' as datetime),1,0) – Peter Vandivier May 18 '18 at 15:59
  • Ok, but my lookeup is ending up in error due to this. Any idea how I can rectify that? – V_immo May 18 '18 at 16:04
  • I recommend updating the question with more detail around the specifics of the error. Perhaps include the exact error message. see here for tips on how to improve the question to get a helpful answer – Peter Vandivier May 18 '18 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.