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 ] Error: Row yielded no match during lookup.
[Lookup ] 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.