API Query not pulling columns with '@' symbol

Matillion ETL
1

I have written a custom Dynamics 365 connector.

Microsoft Dynamics 365 API returns some fields in this format:

statecode@OData.Community.Display.V1.FormattedValue

However when I include those fields in the API Query generator they are dropped in the final config.

If I add them back in manually via Advanced mode, they are not being populated.

0694G00000RqZkcQAF_0D54G00009GQYQDSA5

Is there an issue with Matillion's implementation of CData or are '@' symbols not allowed in CData config? I couldn't find a reference to this in the CData specs:

https://cdn.cdata.com/help/DWE/odbc/default.htm

Any thoughts would be appreciated!

Steve

2

<RANT> I really wish Matillion forum allowed me to edit my posts!! </RANT>

 

 

 

Here is an example of the RESPONSE from Dynamics 365 with the fields that are giving a problem to the API Query component:

0694G00000RqaTNQAZ_0D74G000008ynA0SAI
0694G00000RqaTNQAZ_0D74G000008ynA0SAI927×663 36.2 KB

The API Query component will not return these fields no matter what I’ve tried in the CData config… :disappointed_relieved:

3

Hello SB699925

 

Firstly, the obvious questions:

  1. Have you tried using our Manage Extract API tool or our new Custom Connector tool? They extract the full JSON payload and aren't too bother by what it contains (parameter-wise). These tools are not built using CDATA, so it would also rule out any CDATA limitations, if there are any.
  2. Have you tried using our ODATA Query connector, because this is clearly one of Microsoft's ODATA sources? The link to CDATA documentation you provided refers to the ODBC driver for REST, where API Query uses the JDBC driver for REST.

 

Regards

Ben Smiles

Matillion Product Team

4

Thank you @BenSmilesMatillionProductOwner​ that is brilliant, I hope this has helped @SB699925​ please do let me know if you need anything else.

In the meantime here is the support documentation for Manage Extract API & ODATA Query Connector these should assist in resolving the issue you have been met with.

Thanks and have a great day!

Joe

5

Hey Ben

 

Cheers for that answer, much appreciated!

Turns out we are still running v1.61.6 (although I swear we upgraded to 1.62 last year). Anyhow that's on us. I'm pushing for an upgrade to 1.69 so hopefully that will happen soon!

 

We haven't used the API Extract since the API Query has just felt more flexible. I'll dust that off and have a look.

I didn't realise there was an OData Query connector, I'll have a look at that also!

 

Thanks so much.

6

Hello SB699925

 

May I recommend waiting for 1.70. It lands this week, I believe.

 

Regards

Ben Smiles

Matillion Product Team