Azure Data Factory V2 – Variables

Hi All,
I saw these in the General Activity lists the other day, so I quickly jumped onto the Microsoft documentation, and found there wasn’t much about how to use these.
So, I’ll outline a short example, along with a link to the ARM template to deploy into your own Data Factory.

From what I can tell, there is a new tab in the Pipeline, called Variables, and two new activities – Set Variable, and Append Variable.

There is 3 types of variables we can use. Strings, Boolean and Arrays.

So after a little playing around, I found the first challenge. A variable cannot set itself based on its current value (note: this is as of 2018-10-22, so it may be fixed soon).
For example

Error message when we try to debug.

This is a little annoying as a great use case for these is a counter, but it’s easy enough to work around by creating a temporary counter.
tempCounter = counter + 1 , and then counter = tempCounter

Everything else worked as expected. There is a requirement to do conversions from string to integer for mathematical functions, as shown in my examples.

A short animation of my pipeline (ARM template for this linked below)

ARM Template for this Pipeline is here.

Thank you for taking the time to read this. Do let me know if you find this useful.
Please feel free to give me feedback or ask if there is any specific use cases you are struggling with.

15 Comments

  1. I also saw the “Internal Server Error” problem. Opened a support ticket but the rep couldn’t tell either. After a lot of wrangling, I figured out that this happened because I used a “bool” type as a pipeline variable. After changing it to “string” type (and wrapping checks with string() calls), it worked.

    I see a lot of potential with ADF, but the UX is frustrating, to say the least.

    • Hi Deepak,
      Thank you for visiting, and for the comment.
      My colleague and I have been able to recreate your issue as well. No variation I could think of would work in the “bool” type so this appears to be a bug in the implementation. Your work around works for us as well.

      Rob

  2. Hi Rob,

    I am trying to do the same thing here: creating a counter and use it in the until activity. However, I received “Unrecognized expression” error when using “@equals(int(variables(‘Counter’)),3)” or “@string(add(int(variables(‘Counter’)),1))”. Have you come into the same issue? How did you solve it?

    Thanks,
    -Chi

    • Hi Chi,
      I have replied via email to you as well.
      Your syntax appears correct. I have a vague memory of similar that I just published any way and it worked, or I tried swapping the order of the comparison.
      When I tried this morning, I could no longer get this error so I suspect it may be resolved by Microsoft.

  3. Hi,

    I am sending an http request using a REST API functionality. The problem with the API is that at max it provides 50 records.
    So there is a variabe limit and a variable offset in http request. I want to know how can I store an offset varibale somwhere so that when I apply a for loop or something its value keeps on increasing by 50. Please let me know if I can store this offset variable somehow and can use this in the for loop.

    Http request:

    {
    “id”: “123abc”,
    “method”: “getAllMultiple”,
    “jsonrpc”: “2.0”,
    “params”: {
    “offset”: 0,
    “limit”: 50,
    “order_by”: “last_access”,
    “order_dir”: “DESC”,
    “contact_segment_id”: 385604,
    “start_date”: 1516751332,
    “stop_date”: 1548200932

    }

    • Hi Sunakshi,
      Thank you for the comment.
      You would need to have 2 variables to perform this.
      The first would hold the value you use as your ‘Offset’ , and then another that you use for the calculation.
      How you would achieve this :
      Create two variables – offset (set a default as 0 or 1 depending on what you need) and tempOffset
      Your first request would use the offSet variable as normal (offset will be default)
      After that request, use a Set Variable activity to set the tempOffset = @string(add(50,int(variables(‘offset’)))) — The 50 is your limit.
      And then follow that with a Set Variable activity to set the offset = @variables(‘tempOffset’)

      I haven’t put these expressions into Data Factory (not at home at the moment) but let me know how you go.
      You may need a little bit of logic to handle the offset depending on what value it starts at.
      For example, if offset 0 is the start, and you add 50 to get 50, is that the first 50 records starting from 0, or starting from 1.

  4. Thank you very much for this post! I haven’t found any other sources with variable usage examples (even in official docs). How did you figure out that it is @variables(‘xxx’) instruction?

    • Hi 🙂
      If you add a variable to a pipeline, and select an activity where you you have a field that lets you ‘Add dynamic content’ you will see the variables toward the bottom. If you click on the variable, you will see the @variables(‘xxx’) up the top.

  5. Thank you very much for this post. I have one query regarding usage of variable in Look up activity query, I am trying to right similar to below query.

    SELECT * from @{pipeline().parameters.ConfigTable}
    WHERE SRC_ContainerName = @variables(‘test’)

    However I am getting error for using the same.

    • Hi Prashant,
      Thank you for the comment.
      If you want to use the variable in-line, use it as such.

      SELECT * from @{pipeline().parameters.ConfigTable}
      WHERE SRC_ContainerName = '@{variables(‘test’)}'

      Don’t forget the single quotes around the variable reference if the value of the variable is a string, otherwise SQL will think you are referring to a column.

  6. This doesn’t work

    Failed
    {“code”:”BadRequest”,”message”:”ErrorCode=InvalidTemplate, ErrorMessage=The expression ‘variables(‘vMyVar’)’ is not valid: the string character ‘‘’ at position ’10’ is not expected.\””,”target”:”pipeline/SFTP_TEST/runid/d07b7b85-8539-480e-5580-24332d203c4″,”details”:null,”error”:null}

    • Hi Ed,

      Thanks for the comment.

      From the error message that I see in my email, it looks like the variable name is surrounded by fancy quotes. Have you tried to replace these with just the single quote character?
      For some reason WordPress doesn’t let me show this as it converts all quotes to look the same.

      -rob

Leave a Reply to Ed Cancel reply

Your email address will not be published. Required fields are marked *