Azure Data Factory - Generate custom Guid and copy to SQL table

Question

I need to generate a unique Guid which contains only numeric and of length 20, for every row in the table.

Currently, I am running one lookup activity (name - FetchIDs) to run the Stored procedure. Output of the lookup activity is as shown below

 {
   “count”: 2,
   “value”: [
      {
        “name”: “Test”,
        “ID”: 12345,
        “City”: “City A”,
        “State”: “State A”
      },
      {
        “name”: “Test2”,
        “ID”: 23456,
        “City”: “City B”,
        “State”: “State B”
      }
    ]
 }

Above lookup output might have N number of rows. I would need to add a logic to create a unique custom Guid for each row in above field and then copy this over to another SQL table (named - OutputTable)

I tried using inbuilt guid(‘N’) to generate the unique GUID. However; it was alphanumeric. I wanted to create a unique numerical GuID Of length 20 for each row, before copying it over to the SQL table.

Tried a few options. But, have not been able to come up with a suitable way which can solve it.

Answer 1

Hi SR ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

As per my understanding you are trying to generate a unique Guid that contains only numeric characters and is of length 20 for every row in a table. Please let me know if that is not the requirement here.

uuid() function in mapping dataflow can help to generate guid , but again it would give alphanumeric characters .

To achieve numeric GUID, you can use 'random' function in mapping dataflow it will generate random numeric with partition as seed upto 16 characters , however, concatenating with another random numeric would make it upto 20 characters using substring. Try the below expression in derived column transformation:

substring(concat(ltrim(toString(random(1)),'0.'),ltrim(toString(random(1)),'0.')),1,20)

Alternatively, you can use rand() function in ADF pipeline or in SQL itself to achieve the same.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou