0

I have an online implementation of Dynamics 365 where I need to use Azure Data factory to do the following:

1 - pull some records based on a fetchxml criteria 2 - select random 100 records out of these 3 - insert them into a different entity in D365.

I am using the ADF CDS connector which only supports copy activity (does not support data flows as yet)

What I am hoping I can do is the following:

Task 1 - copy all records into a csv file and add an extra column that contains a random ID as an extra column

Issue here: When I do this, and use the rand() function, all the numbers returned are the same: rand() additional column

enter image description here

The same issue happens if I try to use @guid() all values come back the same.

Question 1 - Is there a reason why rand() and guid() are returning the same values for all records and is there a way to work around it.

Question 2 - Is there another way that I can't think of to achieve what I am trying to do: pick random x number of records from a dataset?

user7415753
  • 188
  • 10

1 Answers1

1

Is there a reason why rand() and guid() are returning the same values for all records and is there a way to work around it?

This is because ADF will execute this expression first and just one time. Then use it's result as the column value. So you get the same data. As a workaround, you need to copy records into csv file first. Then use the csv file as source in Data Flow, then use Derived Column to add randID column. You can also create an Azure Function to add randID column and invoke it in ADF.

Steve Johnson
  • 8,057
  • 1
  • 6
  • 17