ADF pipeline SOURCE with timestamp column as filter

Question

I am trying to build a dynamic query based on lookuptable. The source is using query to fetch data like 'select * from mytable where update > '$updatedate''

The Lookup table contains below structure: I want to use these 2 columns as dataflow parameter in my source as $sourcequery > '$updatedate' where sourcequery is string and updatedate is timestamp. But pipeline returns error as "Operation on target Data Ingestion - Incremental failed: DF-DSL-002 - Parameter stream has parsing errors\nParameter(s) with errors: source_qry,updatedate. Not honoring the datatype of parameter(s) could be one of the causes.","Details":" I tried the concat($source_qry , ' >= ','{$updatedate}'), ut still receiving same error. Any suggestion on above.

Regards,

RP

Answer 1

@Patil Rajas
Thanks for reaching out to Microsoft Q&A.

It seems like you’re trying to build a dynamic query using parameters from a lookup table, but you’re encountering an error related to parsing parameters. The error message suggests that the data types of the parameters might not be honored.

Here are a few suggestions that might help:

1. Check the Data Types: Ensure that the data types of your parameters in the lookup table match the data types expected in your query. For example, if updatedate is expected to be a timestamp, make sure it’s stored as a timestamp in your lookup table.
2. Debugging: Double-check the entire query for any other issues, such as missing placeholders or incorrect column names.
3. Format the Timestamp: If updatedate is a timestamp, it might need to be formatted correctly to be used in a SQL query. You can use a function like TO_TIMESTAMP or FORMAT_TIMESTAMP to format the timestamp.
4. Concatenation Syntax: The error might be due to the way you’re concatenating the strings. In some SQL dialects, you might need to use the || operator instead of + for concatenation.

Here’s an example of how you might format your query:

SELECT * FROM mytable WHERE update > TO_TIMESTAMP('{$updatedate}', 'YYYY-MM-DD HH:MI:SS')

Please replace 'YYYY-MM-DD HH:MI:SS' with the format that matches your timestamp. Remember to replace the placeholders with the actual values from your lookup table. If you’re still encountering issues, please provide more details about your setup and I’ll do my best to assist you further. Hope this helps. Do let us know if you any further queries.