Regular readers of the blog and Azure Data Factory aficionados may recall a post I did last year, where I discussed some of the limitations present as part of the Dynamics 365 / Common Data Service connector. The post focused its attention on the tools inability to map data into two specific field types - Owner and Customer. These differ from the norm of other fields within the Common Data Service database, as they are effectively multi-entity lookup fields. What does this mean in practice? That we can, for example, associate the Owner field to either a User or Team record in the system. The crux of the issue last year ultimately came down to the fact that we had no way of specifying the type of record to write to either of these fields. Hence, they were unsupported, and we had to resort to a bespoke solution to get around the problem.
Bearing all this in mind, I was therefore pleased to not only see the following recent comment from last years post…
…but also the following email from the Azure User Voice site…
Microsoft has, it seems, heard our voices and, as we can see on the Docs website to confirm, this capability is now possible within Azure Data Factory. A big thanks to Kevin and to everyone else who voted for this feature! What’s even better is that it’s effortless to start working with this new feature, which is why I thought I’d show you how in this post.
To set the scene, let’s assume we are importing data from a SQL Server database into the Common Data Service. Specifically, we have many Quote records we wish to import and, also, we want to associate these alongside existing Account records as part of the same import step, using the default customerid field on the Quote entity. And, as you may have guessed already, this field type is indeed that of Customer. We have a list of all the Account GUID values stored in our SQL Server database so that bit is easy - just a straight mapping configured on our Copy Data task:
So far, so good. Next, we need to tell the task which record type we are mapping into - in this case, the Account entity. There are two ways we can do this:
- As helpfully suggested on the Microsoft Docs article referenced above, we can very straightforwardly add on an additional column to our dataset, with a hardcoded values like the one indicated below:
- Next, we then perform a mapping to a “phantom” field - one that doesn’t strictly exist on the database table, but is exposed by the applications Web API to allow callers to tell the Common Data Service database which entity the incoming record is from:
- For more complex scenarios (such as where you need to map to both Contact and Account record types), you will have to resort to logic within your SQL query to generate the appropriate value for each row. The query below shows how you could do this, for when you are linking across your Contact/Account GUID values from separate tables within your database. The query makes an assumption around the table structure and also on the existence of an absolute reference (i.e. the JOINs would not return a match from both the Account AND Contact tables), but for illustrative purposes, should help in adapting it to your specific needs:
- [snippet id=“969”]
Pretty easy right? The same logic will also work with Owner fields now as well, thereby allowing you to associate User or Team ownerships to records within the Common Data Service.
It’s always great to see when a company addresses a significant deficiency in an IT product. This is even more true when the issue relates to something that end-users are providing vocal feedback about. This example does prove the importance of getting your Azure feedback and suggestions logged on the User Voice portal so that the product team can get all the info they need to improve things further. Because let’s face it - some of us may spend many hours of each day using and getting frustrated over the platform for no good reason, so anything that can alleviate this is a good thing. Now that Azure Data Factory has this capability enabled, it does focus additional attention on how effective the product can be as part of one-off data migrations and continuous integrations, a topic which I have been harping on about for some time now. If you’re doing serious work involving tools such as SSIS or others as part of your Dynamics 365 / Common Data Service migrations or integrations, now is the time to start paying attention to Azure Data Factory. Don’t say I didn’t warn you… 😏