I’m doing some Azure Data Factory work and encountered the problem alluded to in the Azure documentation, and I have to say Azure Data Factory variables are problematic.

I created a pipeline which set variables inside a For Each activity. The documentation hints, but should outright say don’t except in whatever circumstances are valid.

Set Variables Documentation

I made the mistake of not seeing and understanding how bad this made things. I created a pipeline and used the Set Variable activity like the diagram shows and it will not behave as you’d expect. In my case and likely to be a very similar experience for many is that it set the variable once and regardless of iterations it never set the value of the variables to anything else. Frustrating and not initially obvious. One good soul in the SQL Server Slack (thanks Brent Ozar) alluded to that variables and parameters weren’t always what they seem, hence I then went and researched; I mean who needs documentation for the Set Variable Activity in ADF(see above); it needed reading

Set Variable inside For Each activity

AS you can see in the diagram following the crumb trail, there’s a pipeline LoadRunner with the Iterate Table and Databases(For Each Actvity) then the If Activity. Then within that I had included Set Variable to feed those values into the next pipeline.

The fix, don’t use Set Variable in this situation and instead add the items directly to the next pipeline into the placeholder parameters. That’ll work much better

Good luck ADFing

See ya round


Leave a Reply

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

Share via
Copy link
Powered by Social Snap