Extracting NEWID from MS SQL Insert

Integrator.io
,
1

I’m creating a flow which inserts records into a MS SQL table; one of the fields is a unique ID created via NEWID().

The insert result appears in the SQL connection debug log:

{"response":[{"value":"7B45B75E-2103-406D-8D9F-8000EF6CCA3C","metadata":{"userType":0,"flags":8,"type":{"id":36,"type":"GUIDN","name":"UniqueIdentifier"},"dataLength":16,"colName":"ExternalExpenseId"}}]}

However, I cannot determine how to map the value returned in the Response Mapping screen.

I’ve tried MANY combinations of mock data, field mappings, JSON, you name it, but I can’t figure out if the SQL server response actually ends up in the Celigo flow anywhere.

Here is my current mock response in my Import step: Pretty sure I’m following the instructions where it says to replace the MockResponse key and data with my own:

[
  {
    "id": "1234567890",
    "errors": [],
    "_json": {
      "id": "1234567890",
      "response": [
        {
          "value": "7B45B75E-2103-406D-8D9F-8000EF6CCA3C",
          "metadata": {
            "userType": 0,
            "flags": 8,
            "type": {
              "id": 36,
              "type": "GUIDN",
              "name": "UniqueIdentifier"
            },
            "dataLength": 16,
            "colName": "ExternalExpenseId"
          }
        }
      ]
    },
    "statusCode": 200,
    "ignored": false,
    "dataURI": "",
    "_headers": {
      "content-type": "application/json; charset=utf-8"
    }
  }
]

I’ve tried _json.response[0].value, response.value, response[0].value, and many other permutations of manually entered paths; for some of these I can see the result in the mapper. However, the value is empty at the next import step.

How can I extract the REAL response MS SQL server is returning?

2

Note: the native default Celigo “id” field isn’t populating during MS SQL inserts. Not sure why.

3

Can you try just _json and then see if you get anything there downstream?

4

I tested further, assigning the _json to another header property. The JSON is just {} an empty brace set.

I am not using bulk insert here because of the need to extract the NEWID value. There are workarounds via SQL which essentially tell the SQL server to store the NEWID value in a secondary table, so I could in theory do the insert then turn right around and lookup the new ID in another flow step. Icky!

Bottom line seems to be that SQL imports via “Use SQL query once per record” do not return an ID (which I expect), nor do they return anything indicated by the connection debugger. Feels like it’s time to log a case.

5

I checked internally and this is currently a limitation. Can you make a subsequent lookup step to fetch it?

6

In this particular situation, I have another unique ID in the data rows (a NetSuite internal ID) that I can use to perform the extra lookup step. A workaround is in place.

I opened a case with support too; maybe it’ll become a feature request?

I’ll post what happens!

7

Sounds good! I’m still checking internally if this is a bug or enhancement. Since we can see the response in debug logs, it seems like a bug :slight_smile:

8

As of now (14 Aug 2025) support ticket indicates that the dev team is reviewing this as a potential bug; so it’s not been kicked back as a “behavior as designed” at least! I’m optimistic.