Microsoft Dataverse Writer

Microsoft Dataverse Writer is a Striim target adapter that writes to Microsoft Dataverse using the Dataverse Web API. The adapter supports writing events to both standard and custom objects of an MS Dataverse instance. The adapter connects to a Dataverse instance using either a manual (password credential grant flow) or automated (Auth code grant flow with public OAuth app) OAuth flow.

The adapter use the ODATA standard REST interfaces of MS Dataverse and supports all basic DML operations. The adapter supports writing groups of events (up to 1000 maximum) in a single OData Batch API. You can configure a property for expiration of this batch.

Summary information and supported objects

APIs used/data supported	Dataverse Web API
Security and authentication	Connects to a Microsoft Dataverse instance using either a manual (password credential grant flow) or automated (Auth code grant flow with public OAuth app) OAuth flow.
Resilience / recovery	A1P
Programmability	TQL or Flow Designer.
Metrics and auditing	The adapter shows the appropriate metrics for successful and discarded rows(events).
Key limitations	A relationship across tables (i.e Navigation Links also like foreign keys) is not supported. Parallelism across tables is not supported.

Create a Microsoft Dataverse Writer application

Create a Microsoft Dataverse Writer application using TQL

The following TQL creates a Striim app that writes to Microsoft Dataverse.

CREATE OR REPLACE APPLICATION MySQLTODv USE EXCEPTIONSTORE TTL : '7d' ;
CREATE FLOW MySQLTODv_SourceFlow;
CREATE SOURCE MySQLTODv_MysqlCDC USING Global.MysqlReader ( 
  Compression: false, 
  Password_encrypted: 'true', 
  Tables: '"waction"."test"', 
  connectionRetryPolicy: 'retryInterval=30, maxRetries=3', 
  FilterTransactionBoundaries: true, 
  Password: '<password>', 
  ConnectionURL: 'jdbc:mysql://127.0.0.1:3306', 
  SendBeforeImage: true, 
  Username: 'root' ) 
OUTPUT TO MySQLTODv_OutputStream;
END FLOW MySQLTODv_SourceFlow;
CREATE OR REPLACE TARGET Microsoft_Dataverse_MySQLTODv_Target USING Global.MicrosoftDataverseWriter ( 
  Tables: 'waction.test,DataverseTest keycolumns(Name) columnmap(Name=name)',
  tenantID: '<tenant ID>', 
  refreshToken_encrypted: 'true', 
  clientSecret_encrypted: 'true', 
  adapterName: 'MicrosoftDataverseWriter', 
  clientSecret: '<client secret>', 
  refreshToken: '<refresh token>', 
  connectionRetryPolicy: 'retryInterval=30, maxRetries=3', 
  BatchPolicy: 'eventCount:999, Interval:60', 
  clientID: '<client ID>', 
  serviceRootURL: 'https://orgc4ca6c06.api.crm.dynamics.com/api/data/v9.2', 
  CDDLAction: 'Halt' ) 
INPUT FROM MySQLTODv_OutputStream;
END APPLICATION MySQLTODv;

Microsoft Dataverse Writer programmer's reference

Microsoft Dataverse Writer properties

Property	Type	Default value	Notes
Batch Policy	Text	eventCount:999, Interval:90	The batch policy includes the event count and interval. Events are buffered locally on the Striim server and sent as a batch to the target every time either of the specified values is exceeded. When the app is stopped, any remaining data in the buffer is discarded. To disable batching, set to `EventCount:1,Interval:0`. With the default setting, data will be written every 90 seconds or sooner if the buffer accumulates 999 events.
CDDL Action	Enum of {Process, Ignore, Halt}	Halt	Any schema change that needs to be applied on the target can only be Halted or Ignored. (Process is not supported).
Client ID	String		Required for authentication with OAuth. The Client ID of the private app registered in the Active Directory of the Microsoft platform.
Client Secret	Encrypted Text		Required for authentication with OAuth. The Client Secret of the private app registered in the Active Directory of the Microsoft platform.
Connection Retry Policy	Text	retryInterval=30, maxRetries=3	With the default setting, if a connection attempt is unsuccessful, the adapter will try again in 30 seconds (retryInterval). If the second attempt is unsuccessful, in 30 seconds it will try a third time (maxRetries). If that is unsuccessful, the adapter will fail and log an exception. Negative values are not supported.
Connection URL	Text		URL endpoint for your Microsoft Dataverse solution.
Refresh token	String		Required for authentication with OAuth. An OAuth 2.0 refresh token.
Tables	String		The name(s) of the table objects(s) to write to, in the format `<table_object>`. The tables must exist when the application is started. When the input stream of the target is the output of a DatabaseReader, IncrementalBatchReader, or SQL CDC source (that is, when replicating data from one database to another), it can write to multiple objects. In this case, specify the names of both the source and target objects. You may use wildcards for the object names. If the reader uses three-part names, you must use them here as well. Note that SQL Server source table names must be specified in three parts when the source is Database Reader or Incremental Batch Reader (`source.schema.%,%`) but in two parts when the source is MS SQL Reader or MS Jet (`schema.%,%`). Examples: `source.emp,Employee__c`: This format matches a specific table from the source to a specific object on the target. `source.mydatabase.Emp%,%`: This format writes to all objects starting with Emp to the target instance. All objects must exist in the target. `source1.%,%`: This format tries to write all matching objects on the target instance. `source1.tab1,Tab1;source2.tab2,Tab2`: This format writes to multiple tables in Microsoft Dataverse. Note Partial wildcards not allowed.For example source.Business%,B% is not an allowed combination Both Object name and field names are case sensitive. Key column support: Since MS Dataverse may have more than one set of primary keys, you may include the keycolumns option in the Tables property to specify a column in the target table that will contain a unique identifier for each row: for example, `Tables:'SCOTT.EMP,mydataset.employee keycolumns(emp_num)'`. You may use wildcards for the source table provided all the tables have the key columns and the target table is specified with its three-part name: for example, `Tables:'DEMO.%,mydb.mydataset.% KeyColumns(...)'`. KeyColumns is mandatory. The target application will halt if keycolumns are not provided.
Tenant ID	String		Tenant Identifier of the directory in which the app is registered.

Microsoft Dataverse Writer runtime considerations

Microsoft Dataverse Writer monitoring metrics

The Microsoft Dataverse Writer monitors the following metrics.

Metric	Description
Number of deletes	Number of delete operations
Number of DDLs	Number of data definition language operations
Number of PKUpdates	Number of primary key update operations
Number of updates	Number of update operations
Number of inserts	Number of insert operations

Microsoft Dataverse Writer limitations

The following are limitations of the Microsoft Dataverse Writer:

The choice field should be present as a String representation of an integer value. For example '2' as a string literal rather than 2 as an integer in the source for it to be successfully processed by the Dataverse Writer.

In this section:

Striim Platform 5.0 documentation