title: SSIS Lookup Transform Fails on Date Column
body: <p>We have a Lookup transform that does a date lookup. The input column data type is DT_DBDATE and so is the matching lookup column. Both are DATE data types in SQL Server 2012. This is an SSIS 2012 package.</p>
  
  <p>The lookup works fine in BIDS (the designer). When deployed and run through the SSISDB UI or through our custom sproc, this lookup fails with the following error:</p>
  
  <blockquote>
    <p>Error: Lookup Status Key.Inputs[Lookup Input].Columns[status_dt] and
    reference column named "status_dt" have incompatible data types.</p>
  </blockquote>
  
  <p>We also see synchronization warnings around all of our DATE columns, those these warning don't result in a failure.</p>
  
  <p>Has anyone else seen this behavior?</p>
  
  <p>UPDATE: The workaround is to cast the columns to DATETIME (DT_DBTIMESTAMP in SSIS) or to change the underlying columns to DATETIME. Both get around the problem. Both are annoying.</p>
  
  <p>My guess is that the source of the problem is in the OLE DB provider. There is probably limited or flaky support for the date only data type.</p>