Problems with Lookup task and dates

I’m trying to make a Lookup transformation with a relation on two string columns and a between dates condition set by parameter:

select * from
(select * from [dbo].[RgnErhverv_Ansvar]) as refTable
where [refTable].[Opretdato] = ? and ? between StartDate and EndDate

where the second parameter is my Date column (FakturaDato_konv) .

My problem is that I keep getting the error:

"Error 1 Validation error. Data Flow Task: Lookup [16505]: input column "FakturaDato_konv" (17826) has a datatype which cannot be joined on"

The StartDate and EndDate is datetime data type and have tried all of the DATE data types for the "FakturaDato_konv" without any luck - DT_DBTIMESTAMP should be the one.

FakturaDato_konv looks like this 24-06-2005 00:00:00
StartDate looks like this 31-08-2001 00:00:00

Any suggestions?

-Jeppe

I tried your lookup string in here and received no error so I don't know specifically what your problem is. You mention above 2 strings and a date but your SQL only has 2 parameters so maybe that is the problem. You should look at the parameters dialog to see if the columns are being joined as you expect. That all being said lookup is a one to one lookup so in general what you are trying to do won't work even if you get the SQL statement problem ironed out.

Thanks,|||

The first parameter is a string value and the second is the date value. The string value is working fine but the date value is causing me problems. I have mapped the parameters to the correct columns in the parameters dialog and manually added the date column (second parameter) to the input columns the advanced editor.

The two date values in my post represent the second parameter and one of the date values in the lookup table – they are same format but I keep getting the has a datatype which cannot be joined on?

I have tried joining on only one date parameter.. same error.

Thanks
-Jeppe

|||

Are there any other errors posted? Have you tried fully qualifying startdate and enddate (e.g. [reftable].[startdate])? Error 1 does not look like an error we post did you cut and paste the entire error (i.e. our errors look like 0xCxxxxxxx)? As I asked previously did you look at the parameter dialog to see the mapping? Did you verify that the metadata for the dataflow column is the type you expect it is?

Thanks,

|||

The warning i like the one I posted and the error messagebox is:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [Lookup [18314]]: input column "FakturaDato_konv" (18427) has a datatype which cannot be joined on.

Error at Data Flow Task [DTS.Pipeline]: "component "Lookup" (18314)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK
The log says:

[DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (18314) failed with error code 0xC0209029. 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.

I have checked the column mappings and the format in metadata. I also tried a simpel lookup only on one of the date columns - samme error.

Maybe my package is corrupted?

Thanks
-Jeppe

|||I found the error

The date column (FakturaDato) had to be mapped to one of the lookup date columns (StartDate or EndDate) - it's not enough that the string column (Ansvar) was mapped and the date columns manually added as input columns in the advanced editor.

Hope this makes sense

Thanks
-Jeppe