How to pass a Date Pipeline Parameter to a Data Flow use in a Dataflow Expression Builder

Question

I am doing something that seems like it should be very easy yet I have yet to figure this out. I have read countless posts and tried everything I can think of and still no success.

Here goes:

I created a Pipeline Parameter pplLastWritten with a default value of 2022-08-20 12:19:08 (I have tried without the time for troubleshooting and still get errors)

Then I create a Data Flow Parameter ptblTableName

I have tried to convert to a Date, keeping as is and converting later...you name it still errors out.

In the expression builder I tried this and many more ways to build out to a sql statement:

"SELECT * FROM xxxxxx."+$ptblTableName+"where Lastwritten>='{$ptblLastWritten}'"

This is the post I got the idea from: ADF data flow concat expression with single quote

This is the error I got most of the time.

Operation on target df_DynamicSelect failed: {"StatusCode":"DF-Executor-StoreIsNotDefined","Message":"Job failed due to reason: at Source 'RptDBTEST'(Line 5/Col 0): The store configuration is not defined. This error is potentially caused by invalid parameter assignment in the pipeline.","Details":""}

I have tried so many things but in the end nothing has worked. I am new to Data Factory and come from the SSIS world which was so much easier. I would greatly appreciate someone helping. Please explain this like I'm a kindergartener because this tool is making me feel like it. :) Thank you in advanced.

I have tried various ways to format

Using different ideas in the expression builder

the ideas in this post: ADF data flow concat expression with single quote

Answer 1

You can use concat() function in the Data flow dynamic expression like below.

Here is the sample data in SQL.

I have created two dataflow parameters mytable and mydate.

Passed the values like below. Check the expression checkbox. For date you can also pass like this '2022-11-07T00:00:00.0000000'.

In the Query option use below Expression.

concat('select * from dbo.',$table_name,' where mydate >=','\'',$mydate,'\'')

Values inserted in Target table.