How do you perform a Lookup transformation using an input column of type DT_DATE (e.g. from a spreadsheet input) and a SQL Server table column that has the datetime type? Dragging the former onto the latter results in the error message "One or more columns do not have supported data types, or their data types do not match".
The answer is to create a Data Conversion transformation earlier in the flow. In this task, convert your input column to the type "database timestamp [DT_DBTIMESTAMP]". Make a note of the output alias you have entered. Now go back to the Lookup transformation and drag the new column (i.e. <output alias> of the Data Conversion transformation) onto the datetime column in the lookup table.