Aaron H. Kim Fearless Integration Maniac

Move data from AX/CRM Online to Azure DW using OData connector(OAuth)


Introduction

Microsoft had silently released OAuth support in OData connector for Azure Data Factory starting from 1 November 2016.

Hi Aaron, the OAuth support for OData is just officially released and you can give it a try now. The documentation change on this topic is ready there pending publish. Expect you can see that tomorrow.

https://disqus.com/home/discussion/thewindowsazureproductsite/move_data_from_odata_sources_azure_data_factory/?utm_source=reply&utm_medium=email&utm_content=read_more#comment-2978680218

As the documentation states, OAuth support is still limited.

OAuth (note Azure Data Factory currently only support Azure Active Directory based OAuth).

What this means is that you could use this feature to connect Microsoft services like AX/CRM Online but not for other SaaS like Google Analytics, facebook, 3rd party OData endpoints requiring different authorization server. In that case, you could still use Azure Data Factory but will need to create a custom activity(basically your own component requiring coding exercise which takes a lot more efforts) and run it in HDinsight or Azure Batch.

I have been waiting for this feature(OAuth support) for quite long - 6 months since Microsoft released OData Connector. Now, I welcome its release and gladly tested moving data from AX/CRM Online to Azure Data DW tables. Here are list of things that I find still incomplete and requires a workaround.

Copy data(PREVIEW) tool

Generally, you have a lot of tables and columns to integrate from AX and CRM. Having to type them all by yourself is tedious and error prone. Copy data(PREVIEW) tool is really helpful in generating and formatting json objects(Linked Service, Datasets, Tables) automatically.

Sometimes, however, it fails to generate the end results for various reasons(e.g. type conversion fails, connection fails, etc.) In that case, really painfully, you will need to go back to the authoring tool.

Type conversion isn’t always right

As the documentation states,

Copy activity performs automatic type conversions from source types to sink types with the following 2-step approach:

  • Convert from native source types to .NET type
  • Convert from .NET type to native sink type

When moving data from OData data stores, OData data types are mapped to .NET types.

Problem

However, converted .NET type from OData data type isn’t always right. Look below. It converted OData data type to Object .Net type when it should have been String.

Workaround

In that case, you will have to manually change its type in source table definition to a supported one as above.

Fails after 66 mins of execution

Problem

When the Copy activity runs more than 66 mins then end up with following error.

Copy activity encountered an unknown server error at Source side: Code: 0 ; Message: ‘Type=Microsoft.Data.Mashup.MashupCredentialException,Message=The credentials provided for the OData source are invalid. (Source at https://xxx.crm6.dynamics.com/XRMServices/2011/OrganizationData.svc/ContactSet.),Source=Microsoft.DataTransfer.Common,’.

The error message itself doesn’t actually make sense as the credential still works when follow up process starts.

I am not 100% sure whether there is a timeout limitation for Copy activity or not. I have reported the issue to Microsoft to find out root cause and waiting for an answer.

Workaround

I managed to get it working by increasing number of cloudDataMovementUnits to use from 1(default) to 4. This made copy process to complete quicker(within 60 minutes) hence didn’t end up timeout. Please refer to Copy Activity performance and tuning guide.

            {
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "RelationalSource",
                        "query": "ContactSet"
                    },
                    "sink": {
                        "type": "SqlDWSink",
                        "allowPolyBase": false,
                        "writeBatchSize": 0,
                        "writeBatchTimeout": "00:00:00"
                    },
                    "enableStaging": false,
                    "cloudDataMovementUnits": 4
                }

AuthorizedCredential generated doesn’t work

Problem

AuthorizedCredential is an OAuth token generated when you click Authorize button either on Authoring tool or Copy Data tool using your own credential(email/password) for AX/CRM online. I successfully generated correct AuthorizedCredential for the CRM Online instance with a service account’s email/password in a single environment(DEV).

Then, I tried to use the same AuthorizedCredential in another Data Factory(UAT). But, it gave me an error saying that the AuthorizedCredential I tried to use is expired. But, it isn’t expired and works just fine in my original(DEV) environment. What this means is that AuthorizedCredential is somehow only allowed to be used in a particular Data Factory which it was originally generated from.

Fine, I can just regen and use a different AuthorizedCredential for each environment then, I thought.

The result was different for AX and CRM in this instance.

For AX, no problem. For CRM, AuthorizedCredential gets generated straight after I enter an email address(and before entering password) from popup => This is weird, shouldn’t I enter password as well? It was supposed to bring me to another login dedicated for the company but pop up just closes and instead give me an AuthorizedCredential which doesn’t work.

Workaround

I have not found a workaround for this issue yet but reported to Microsoft for an answer. As it works from one environment(DEV), I will test further from there for now.


Similar Posts

Comments