Updating Dynamics365 from Azure Data Factory

 Updating an entity in Dynamics365 from Azure Data Factory should be easy right? I mean, they're both Microsoft products, there's a native connector for Dynamics365 in ADF and it all seems to connect up ok to pull data from Dynamics so should be straight forward. Well, turns out it's not so grab a coffee because this one takes some explaining.

Now to be fair, a lot of this is documented on various other posts, but there was a fair bit of subtlety and nuance. Hopefully this post has all the bits so if I ever need to do this again it will be a piece of cake.

TL;DR;

For any lookup field/column in the entity:

• Create a derived column with a name of "<field schema name>@odata.bind", e.g. for field my_accountid the column name needs to be "my_AccountId@odata.bind" with the quotes
• The value of the column needs to reference the lookup table with an 's' added to the end, e.g. my_accounts field is an account id from the account table so the value needs to be /accounts(<accountid guid>) 
• in the mapping, the target needs to be the schema name with @odata.bind on the end, e.g my_AccountId@odata.bind, so the mapping in this example would be ["my_AccountId@odata.bind" -> my_AccountId@odata.bind] 

Scenario Setup

So for this actual bit of work we were replacing a feed from a SQL Server database view that updated a Dynamics entity. The original feed was a PowerApps pull from the database, but security didn't like that so we needed to create something to push to Dynamics instead.

Service principal access works correctly, data is good and entities are all ready to go. For this post I'll simplify the schema structure and not dwell too much on the specifics other than where there were issues

Approach 1 - Copy Data Activity

 For a simple first cut I tried a simple Copy Data activity. This actually worked really well - grab data from the database view and push it into the table, everything flowed nicely and the data was visible in the front end.

Except ...

The primary key values in the entity weren't known by the database so we'd get duplicate records. We could've used an alternate key, but that would require marking the field as an alternate key in Dynamics and the team wasn't keen to do that at this stage, due to other work and lack of time for testing.

It was easy enough to add steps to do a data pull from the entity, match the data using our alternate key and then write the data back to the entity, and this approach did work, but was clunky and slow. We'll keep this as the fallback solution, but meanwhile try ...

Approach 2 - Data Flow with Lookup

Next approach was to use a data flow with a lookup onto the entity, using our alternate key as the join and then the primary key for an upsert. First stab at this worked well, but some columns weren't updated. From here, things turned sour pretty quick.

The columns that weren't updating were Lookup fields in the entity. For the explanations that follow we'll call the Dynamics entity my_servicedelivery and the lookup field my_accountid, which is a guid column referencing the account entity.

The data flow was pretty simple; 2 sources - 1 for the view and 1 for the entity, a left join between the view data and the entity, an Alter Row upsert using the my_servicedeliveryid key and then the sink to the entity,

 

The Errors

Microsoft.OData.ODataException: An undeclared property 'my_accountid' which only has property annotations in the payload but no property value was found in the payload 

 The first error encountered was "Microsoft.OData.ODataException: An undeclared property 'my_accountid' which only has property annotations in the payload but no property value was found in the payload"

Turns out that entity attributes have both a logical name and a schema name. The logical name is what you see in the attributes for mapping and in the Dynamics database tables, the schema name is a camel case version of the logical name, so in this case logical name my_accountid had a schema name of my_AccountId. 

This error means you need to use the schema name, rather than the logical name. MS documentation states that you should use schema name for any custom attributes, but I found it was only necessary for the lookup ones

You can use a tool like FetchXML builder in Xrm Toolbox  to find the schema name and also see if the attribute is a lookup type.

Updating the column name in the view fixed this problem, leading to ...

Microsoft.OData.ODataException: A 'PrimitiveValue' node with non-null value was found when trying to read the value of the property 'my_AccountId'; however, a 'StartArray' node, a 'StartObject' node, or a 'PrimitiveValue' node with null value was expected.

 Now, this error has been reasonably well noted, but there are a few nuances to it. Basically, Dynamics is getting a string value from ADF, but wants an object.

There are 2 bits to this; 

1. The column name needs to be the schema name with @odata.bind added to the end of it, so in this example the my_accountid source column needs to be called my_AccountId@odata.bind
2. The value of the column needs to reference the lookup entity, which in this case would be account, but it needs to have an s on the end and be in the format /entitys(guid). So for this example the value in the column should be '/accounts(<guid for the accountid)'

Ok, easy enough, update the view to have a column called [my_AccountId@odata.bind] and set the value to be CONCAT(N'/accounts(', my_accountid, N')'). Job done.

Except it wasn't. The debug run still failed with the same error.

ADF, and maybe Dynamics, doesn't handle the column name in the view very well and doesn't present it properly (or Dynamics doesn't treat it properly). So, we roll back the view changes and instead create a derived column in ADF before the upsert, setting the column name to have the @odata.bind on the end of the name and the concat logic in the expression. After a bit (ok, quite a bit) of playing with different names and expressions it turns out the column name also needs to be in double quotes, "my_AccountId@odata.bind"

 

So that must be it, right? Well, not quite. Same error.

The last step was to set the target column in the sink mapping to also be in the format schema_name@odata.bind, as shown in the JSON script snippet from the dataflow

 

 Once all of this was set the flow debugged correctly and data was updating as expected.

In our actual data flow we had multiple lookup fields, so created multiple derived column transforms prior to the Upsert but the mapping principle was the same for each field.

This took a bit of time to get sorted, but having the lookup to get the entity id does work better for performing updates and is a lot safer in avoiding duplicates. 

Entra Provisioning Expressions FormatDateTime and IIF

 Lately when configuring an Entra ID Enterprise Application for SCIM integration to an external system, I ran into an issue passing through dates. The cause of the problem was that we use an extension attribute to hold this particular date, but we also add text into the field.

My first inclination was to use the IIF expression to do some basic format checking, but this fails as the FormatDateTime function is eager and checks for the value of the field before processing the IIF part. For example this statement (simplified)

IIF(Len([extensionAttribute9])="10", FormatDateTime([extensionAttribute9], "dd/MM/yyyy", "yyyy-MM-dd"), "")

will fail if [extensionAttribute9] has non date values.

The fix was to move the IIF statement inside the FormatDateTime expression, so the example above becomes

FormatDateTime(IIF(Len([extensionAttribute9])="10", [extensionAttribute9], ""), "dd/MM/yyyy", "yyyy-MM-dd")

Moving the logic inside the FormatDateTime expression means that the eager validation evaluates the logical function before applying the value.

IIF Limitations

Something else that came up was limitations in how IIF behaves, notably that it doesn't support AND or OR for multiple comparisons. This is well documented on the expressions page, https://learn.microsoft.com/en-us/entra/identity/app-provisioning/functions-for-customizing-application-data#iif, but did require a rethink.

In the end I found it worked best to check for not equal rather than equal. The final expression ended up as 

FormatDateTime(IIF(IsNullOrEmpty([extensionAttribute9]), "", IIF(Len([extensionAttribute9])<>"10", "", IIF(Instr([extensionAttribute9], "/", , )<"1", IIF(Instr([extensionAttribute9], "-", , )<"1", "", [extensionAttribute9]), [extensionAttribute9]))), "32", "dd/MM/yyyy", "yyyy-MM-dd")

The crude check being check that it's not null or empty, then check for a length of 10 which fits our date format, then check for the presence of "/" or "-" characters and if these all pass then format as date from "dd/MM/yyyy" to "yyyy-MM-dd". Obviously this isn't a super accurate check but given the limitations with the provisioning expressions it should cover most cases and we can update source data for any failures.

Local Testing an Azure Function with a Timer Trigger

Following on from the last post on  Azure Function Deployed But Not Visible, when investigating this I wanted a way to test the function without having to deploy it to Azure. Here's how I did it, using Visual Studio Code and Postman API.

Extensions 

 First up was to install a couple of extra extensions in VS Code; Azurite and Azure Functions Core Tools.

Azurite is an extension that allows "mocking" Azure Blob Storage functionally and can be installed from the VS Code Extensions tab. More info on installing can be found here, https://learn.microsoft.com/en-us/azure/storage/common/storage-install-azurite?tabs=visual-studio-code%2Cblob-storage, and the page has links to other pages on what Azurite is and how to use it.

Azure Functions Core Tools is the tool set that lets you run functions locally. You can try installing by hitting the F1 key then choosing Azure Functions: Install or Update Azure Functions Core Tools but may find you need to download and install manually. The download and further instructions can be found here, https://learn.microsoft.com/en-us/azure/azure-functions/functions-run-local

Running the Function

Before we can run the function we need to start the function runtime environment. To do this, hit the F1 key then find and select Azure Functions: Start. When this runs you'll get prompted to log into Azure and select the subscription and resource group you want to use. Alternatively enter func start in the terminal window. I've found the Azure Functions: Start command a bit hit and miss, so using func start seems to work more consistently.

 

Note that for Python, you'll need to start the Python virtual environment if using that config, otherwise you'll get errors about modules not being found. This will be something like running & <project_path>/.venv/Scripts/Activate.ps1 in the terminal.

 

Postman

Once the function is running in VS Code you'll be able to send a Postman request to trigger the function. By default the function runs on port 7071 (I don't know how to change this, as this worked fine for me) so the call for Postman will be a POST request to http://localhost:7071/admin/functions/function_name, e.g. http://localhost:7071/admin/functions/MyTestFunction. 

 

Authentication isn't needed, but there does need to be a body, so if the function doesn't take parameters then just add empty brackets to the Postman request body. 

Here's how the request should look

 

 

All going well, Postman will get an HTTP 202 response, and function output will appear in the VS Code terminal window.

Settings etc.

The functions local runtime uses settings in the local.settings.json file, so these might need to be changed temporarily for testing. Also, if using service principals or managed identities for any resource authentication these will probably fail, so updating these to a different auth (e.g. SAS key or SQL login) might be needed for testing.

 

Azure Function Deployed But Not Visible

 Playing around with Azure functions lately and struck a weird issue when trying to get the function running up in Azure (as opposed to just testing on my laptop).

 This particular function is a Python script, developed and deployed from Visual Studio Code to an existing Function App. The function behaved correctly in the VS Code dev environment, and appeared to deploy fine with no errors, however when checking in the Azure Portal there was no function to be found. Deleting and recreating the Function App didn't help

The fix was to add "AzureWebJobsFeatureFlags": "EnableWorkerIndexing", to the local.settings.json file, so it looks something like

{

  "IsEncrypted": false,

  "Values": {

    "FUNCTIONS_WORKER_RUNTIME": "python",

    "AzureWebJobsFeatureFlags": "EnableWorkerIndexing",

  }

}

 In addition, the .funcignore file was excluding local.settings.json, so I removed this as well.

Once this was done and the function redeployed everything turned up as expected 

SSIS For Loop AssignExpression Error

 Just a quick note on a frustrating error adding an AssignExpression to an SSIS For Loop Container.

The expression being added was

@[User::LoopCount] = @[User::LoopCount] + 1

which looks fine, but continually gave an error of "the equals (=) sign at position 20 was unexpected". 

Changing the variable formats, and other syntactic changes didn't help. And the cause?

A space at the front of the expression. If I'd counted to position 20 I might have found it earlier, as the = sign was at position 19, but then again, probably not. Removing the space solved the error and allowed the loop to run.

Azure Data Factory Metadata-Driven Pipelines - 2

 As mentioned in the previous post, we're using some custom tables behind our metadata-driven pipeline to provide some flexibility and hopefully allow it to be expandable. The schema is still a work in progress, and has some obvious limitations that could be improved on once we have some more time available.

Overview 

High-level, the solution has some tables to hold the entities/tables that are to be loaded, along with column mappings, "high water" values for delta loads, and any pre-copy scripts to be run. Here's the ERD.

 

 

Table	Purpose
DataFeed	High level data feed details
DataFeedEntity	Tables to load
ColumnName	Column names to cut down duplication in table rows
DataFeedColumn	Source and destination columns
DataFeedEntityScript	The pre-copy script to run if required
HighWaterValue	Column and value used for delta loads

Most of the tables have an "Active" column which provides flexibility of which entities and columns to include in the ADF copy activities.

On top of the tables is a view which is used to present the table data to the ADF pipeline. In theory it should be possible to have multiple views for different data loads, but we haven't tested this yet.The easiest way to create the view is to use the table that the ADF wizard creates then recreate the table output in the view. I'll include our current version below for reference. There's also a stored proc to update the high water values, and a table valued function which gets a count of active entities to be loaded.

Limitations

There're a few baked in limitations in our solution which were design choices based on our loads and keeping it simple (ish) to start with. These should all be easy to adapt and we'll probably look at that in the down time. The key limitations are:

• we assume the destination table has the same name as the source entity
• also assume that the target schema is dbo. This was a bit of laziness and we'll probably add a destination schema column to the DataFeedColumn table in the near future
• the data type columns in DataFeedColumn are the ADF data types, e.g. String rather than varchar. We have a separate mapping table that we use when populating the table, but this could be added to the schema 
• We don't store connections in these tables, which could be a useful enhancement 

Example SQL View

SELECT CONCAT (
            N'{
            "entityName": "',
            dfe.DataFeedEntityName,
            N'"
        }'
            ) AS SourceObjectSettings,

        NULL AS [SourceConnectionSettingsName],

        NULL AS [CopySourceSettings],

        CONCAT (
            N'{
            "schema": "dbo",
            "table": "',
            dfe.DataFeedEntityName,
            N'"
        }'
            ) AS[SinkObjectSettings],

        NULL AS [SinkConnectionSettingsName],

        concat(N'{
            "preCopyScript": ', ISNULL(QUOTENAME(pre.ScriptBody, '"'), 'null') ,
            ',
            "tableOption": null,
            "writeBehavior": ', CASE WHEN dfe.LoadType = 'FullLoad' THEN '"insert"' ELSE '"upsert"' END, ',
            "sqlWriterUseTableLock": true,
            "disableMetricsCollection": false,
            "upsertSettings": {
                "useTempDB": true,
                "keys": [
                    "', keycols.KeyColumnName, N'"
                ]
            }') AS [CopySinkSettings],

        REPLACE(CAST(N'{
            "translator": {
                "type": "TabularTranslator", 
                "mappings": [{X}]
             }
          }' AS NVARCHAR(max)), N'{X}', ca.X) AS [CopyActivitySettings],

        N'MetadataDrivenCopyTask_ftq_TopLevel' AS [TopLevelPipelineName],

        N'[
            "Sandbox",
            "Manual"
        ]' AS [TriggerName],

        CONCAT (
            N'{
            "dataLoadingBehavior": "',
            dfe.LoadType,
            N'",',
            N'"watermarkColumnName": "',
            cm.ColumnName,
            N'",',
            N'"watermarkColumnType": "DateTime",',
            N'"watermarkColumnStartValue": "',
            convert(VARCHAR(40), hv.TimestampValue, 126),
            N'"',
            N'}'
            ) AS [DataLoadingBehaviorSettings],

        dfe.EntityGroup as [TaskId],

        dfe.Active [CopyEnabled],

        ROW_NUMBER() OVER(ORDER BY dfe.[EntityGroup], dfe.[DataFeedEntityId] DESC) AS RowNumber,

        dfe.DataFeedEntityId

    FROM adf.DataFeedEntity dfe
    JOIN (
        SELECT c.DataFeedEntityId,
            STRING_AGG(N'{"source":{"name":"' + cast(c.[SourceColumnName] AS NVARCHAR(max)) + N'",
            "type": "' + c.SourceDataType + '"},"sink":{"name":"' + cast(c.[DestinationColumnName] AS NVARCHAR(max)) + '"}}', ',') X
        FROM adf.vw_ColumnMapping c
        GROUP BY DataFeedEntityId
        ) ca ON ca.DataFeedEntityId = dfe.DataFeedEntityId
    LEFT JOIN adf.HighWaterValue hv ON dfe.DataFeedEntityId = hv.DataFeedEntityId
    LEFT JOIN adf.ColumnName cm ON hv.ColumnNameId = cm.ColumnNameId
    LEFT JOIN (
        SELECT  c.ColumnName as KeyColumnName, 
                dfc.DataFeedEntityId
        FROM [adf].[DataFeedColumn] dfc
        JOIN [adf].[ColumnName] c ON dfc.DestinationColumnNameId = c.ColumnNameId
        WHERE dfc.IsKey = 1
    ) keycols ON dfe.DataFeedEntityId = keycols.DataFeedEntityId
    LEFT JOIN adf.DataFeedEntityScript pre ON dfe.DataFeedEntityId = pre.DataFeedEntityId
        AND pre.ScriptType = 'PreCopy'
    WHERE dfe.Active = 1 

 

 

Part 1: Azure Data Factory Metadata-Driven Pipelines - 1

SSIS Azure SQL Connection Login Failure

 Issue

Running an SSIS package using environment variables failed when trying to connect to an Azure SQL database. The error returned pointed to a password issue with the SQL user.

Solution

There was a couple of issues at play here.

1. The server name component of the connection string needed to have the full Azure database name, i.e. my-sql-server.database.windows.net rather than just my-sql-server
2. We also had to add the Persist Security Info=true parameter to the connection string. I think this is because the user we connect as is a contained user to the specific database.

Also worth noting that as we use a contained user the database needs to be specified in the connection string