Parameter in Data Flow SQL Query

Question

Hi Guys,

I want to use Parameter (Date) in SQL Query of a Data Flow in ADF via Pipeline, where the value is coming from a pipeline Variable.

I could pass the paramter but the execution is giving me a following error.

The store configuration is not defined. This error is potentially caused by invalid parameter assignment in the pipeline..

Now there is a solution available for a similar but not exact issue which is to check or uncheck the Expression checkbox of a Data flow parameter, so I did following steps.

1. Did check/uncheck the Expression checkbox for the parameter (remember, this is Date datatype parameter, so expression is not valid for Date type Parameter). I did it by first passing the string parameter (with checked expression checkbox) and than let Dataflow convert the string to date and store it inside another Parameter which is date).
2. I tried passing directly to Date type parameter to Dataflow parameter (here expression checkbox is not applicable for a reason).

None of the above trials worked.

Note, I don't want to use the Dynamic Content in Data Flow query as the Source Query is complicated and Concat will be a big mess in that case

Appriciate your inputs

Peace, V

Answer 1

Hi @Viral Kothari ,

Welcome to Microsoft Q&A platform and thanks for posting your question.

As I understand your query, you are trying to write sql query in dataflow by passing the value for dataflow parameters via pipeline variable . Please let me know if my understanding about your requirement is incorrect.

I was able to reproduce the error you are facing. The reason why expression checkbox is disabled for parameter having date datatype is it is applicable only for string type parameter to convert as an expression.

You can try the following workaround to mitigate the issue:

1. In dataflow , create the parameter having string datatype .
2. In source transformation , use toDate function to convert the string value of dataflow parameter to date value. For Example: "select * from TableName where ColName='{toDate($Param1)}'"
3. In pipeline which is invoking the dataflow, create the variable of type string
4. In dataflow activity under Parameter tab, provide the date value under single quotes using dataflow expression and check the expression box.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Hi, I want pass the string parameters as Timestamp format, Because I need to retrieve data which are greater that the the input timestamp, If we use ToDate it consider only date part not time part, but when I use ToTimestamp instead of ToDate then I am getting error