tMicrosoftCrmInput Standard properties

Authentication Type

CRM Version

Select the version of the ON-PREMISE CRM database, CRM 2011 (deprecated), CRM 2015 (deprecated), or CRM 2016.

ON-PREMISE 2016 is implemented using Apache Olingo OData 4.0 libraries. For more information, see http://olingo.apache.org/doc/odata4/index.html.

Mode

Select the ON-PREMISE CRM2016 authentication mode, NTLM or OAUTH 2.0.

This option is available when ON_PREMISE is selected from the Authentication Type drop-down list and CRM 2016 is selected from the CRM Version drop-down list.

Flow

Select the authentication flow to obtain the authentication token: Authorizationcode or Resource Owner Password Credentials.

This option is available when you select ON_PREMISE from the Authentication Type drop-down list, CRM 2016 from the CRM Version drop-down list, and OAUTH 2.0 from the Mode drop-down list. Before the Resource Owner Password Credentials (ROPC) authentication flow gets available, the Authentication Code flow is used to obtain the token. See Microsoft identity platform and OAuth 2.0 Resource Owner Password Credentials for information about ROPC.

API Version

Select the API version of the ONLINE CRM database, v2007 (deprecated), v2011 (deprecated), v2016 (odata), or v2018.

ONLINE 2016 and 2018 are implemented using MS Dynamics WebAPI v8.1 (2016) and v9.1 (2018). For more information, see https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/use-microsoft-dynamics-365-web-api?view=dynamics-ce-odata-9.

You must set the Service Root URL to WebAPI v9.1 or higher when you select v2018 (use v8.1 when selecting v2016) as defined in the default value.

This option is available if ONLINE is selected from the Authentication Type drop-down list.

Registered application type

Select the type of your application, either Native App or Web App with delegated permissions.

This option is available for CRM ONLINE 2016 and 2018.

Microsoft WebService URL

The Web Service URL of an on-premise CRM server.

This option is available for CRM ON-PREMISE 2011.

OrganizeName

The name of the organization that needs to access the CRM database.

This option is available for CRM ON-PREMISE 2011, ONLINE 2007, and ONLINE 2011.

Username and Password

The user authentication data required to access the CRM database.

To enter the password, click the [...] button next to the password field, enter the password in double quotes in the pop-up dialog box, and click OK to save the settings.

Domain

The domain name of the server on which the CRM database is hosted.

This option is available for CRM ON-PREMISE 2011 and 2016.

Host

The IP address of the CRM database server.

This option is available for CRM ON-PREMISE 2011, ON-PREMISE 2016, and ONLINE 2007.

Port

The listening port number of the CRM database server.

This option is available for CRM ON-PREMISE 2011.

Organization Wsdl URL

The WSDL URL of the Organization Service.

This option is available for CRM ON-PREMISE 2015.

Security Service URL

The URL of the Security Token Service.

This option is available for CRM ON-PREMISE 2015.

Trust Certificate

The path to the Trust Certificate file.

This option is available for CRM ON-PREMISE 2015.

Discovery service endpoint

The CRM online discovery service endpoint.

Because Microsoft regional Discovery service has been deprecated, make sure to use this endpoint URL to replace the one in this field: https://globaldisco.crm.dynamics.com/api/discovery/v2.0/Instances.

See topic Error code: DynamicsFailedToConnect at Troubleshoot Azure Data Factory connectors and Modify your code to use global Discovery Service for related information.

This option is available for CRM ONLINE 2011.

Service Root URL

The root URL of the service to be connected.

This option is available for CRM ON-PREMISE 2016, ONLINE 2016 and 2018.

Application ID

The unique client ID that Azure AD assigns to your application when registering the application in the Azure portal. Note that the default client ID displayed in this field is for testing purpose. You need to apply a client ID of your own in actual use. For information on applying a client ID, see Quickstart: Register an application with the Microsoft identity platform.

This option is available for Microsoft CRM ONLINE 2011, 2016, and 2018.

Application secret

The access key for your web application. For more information about how to get its value, see Granting permissions to the application.

This option is available when Web App with delegated permissions is selected from the Registered application type drop-down list.

Token endpoint

Enter the URL of the endpoint to retrieve the authentication token from.

This field is available when Resource Owner Password Credentials is selected from the Flow drop-down list.

OAuth authorization endpoint

The OAuth authorization endpoint to be used for the OAuth discovery. For more information, see Endpoints.

This option is available for Microsoft CRM ONLINE 2011, 2016, and 2018.

This option is not available if Resource Owner Password Credentials is selected from the Flow drop-down list.

Redirect URL

Enter the OAuth 2.0 redirect URL.

This field is not available if OAUTH 2.0 is not selected from the Mode drop-down list or Resource Owner Password Credentials is selected from the Flow drop-down list.

Time out (second)

The number of seconds to wait for a reply message from the CRM server before closing the connection to it.

Entity/EntitySet

Select the relevant entity/entity set from the list or select CustomEntity/CustomEntitySet from the list, and then in the Custom Entity Name field displayed, specify the entity/entity set name and define the schema for the custom entity/entity set.

Schema and Edit schema

A schema is a row description. It defines the number of fields (columns) to be processed and passed on to the next component. When you create a Spark Job, avoid the reserved word line when naming the fields.

• Built-In: You create and store the schema locally for this component only.

• Repository: You have already created the schema and stored it in the Repository. You can reuse it in various projects and Job designs.

Click Edit schema to make changes to the schema. If the current schema is of the Repository type, three options are available:

• View schema: choose this option to view the schema only.

• Change to built-in property: choose this option to change the schema to Built-in for local changes.

• Update repository connection: choose this option to change the schema stored in the repository and decide whether to propagate the changes to all the Jobs upon completion.

  If you just want to propagate the changes to the current Job, you can select No upon completion and choose this schema metadata again in the Repository Content window.

The schema of this component is related to the specified entity.

Select this check box and in the field displayed, specify the search criteria that uses the standard OData query functions such as contains, endswith, and startswith. For more information about the supported query functions, see the description of Standard query functions on Query Data using the Web API.

This option is available for CRM ON-PREMISE 2016, ONLINE 2016 and 2018.

Logical operator used to combine conditions

Select the logical operator used to combine the conditions defined in Conditions table.

• And: the record will be included in the result set when all of the conditions are met.

• Or: the record will be included in the result set when any of the conditions is met.

Conditions

Click the [+] button to add as many conditions as needed and specify the following parameters for each condition:

• Input column: Click in the cell and select the column of the input schema on which the condition is to be set.

• Operator: Click in the cell and select the operator used to compare the value for the input column with the value defined in the Value column. The values for this parameter are: Equal, NotEqual, GreaterThan, LessThan, GreaterEqual, LessEqual, and Like.

• Value: Type in the value between quotes if needed. If you are using CRM ONLINE or ON-PREMISE version 2016 (or a later version), to filter data using a column that is an ID (for example, salesorderid), you need to enter an expression in this field that converts the ID into a Java UUID, such as java.util.UUID.fromString("your_salesorderid").

Select this check box and in the table displayed, specify the column(s) based on which the retrieved data will be sorted in ascending or descending order. Multiple sort columns can be specified and the hierarchy of the sorting is based on the order of the columns specified. That is to say, the retrieved data will be sorted by the first column and then that ordered list is sorted by the second column, and so on.

This option is available for CRM ON-PREMISE 2016, ONLINE 2016 and 2018.

Reuse Http Client

Select this check box to retain the current connection or clear it to release the connection.

This option is not available for CRM ON-PREMISE 2015.

Max number of reconnection attempts

The maximum number of reconnect attempts to the CRM database after the token is expired. If the value is set to 0 or less than 0, no reconnect attempt will be made.

This option is available for CRM ON-PREMISE 2016, ONLINE 2011, 2016, and 2018.

Force OAuth resource

Select this option to use an OAuth resource other than the default. Enter the OAuth resource URL in the Resource field. If this option is not selected, the resource will be the host of the Service Root URL option from the Basic settings view.

This field is available when OAUTH 2.0 is selected from the Mode drop-down list.

Expand entity

Retrieve expanded entity properties and write the properties to dedicated columns. Expanded entity properties are written in columns as JSON strings.

To retrieve and write specific properties of an expanded entity to a column, add a row in the table by clicking the plus button under the table, select from the drop-down list the expandable column that references the expanded entity in Expandable column, enter the desired properties of the expanded entity in the OData query option column, and select the destination column from the drop-down list in Destination column column.

You can specify one or multiple properties in the OData query option column in the format of $select={property_1_name},{property_2_name},.... At least one property needs to be provided. To retrieve and write all the properties of the expanded entity, enter $select=* in this column. See How to build an OData Service with Olingo V4 Part 5.2: System Query Options: $select, $expand for related information.

This field is available with the Authentication Type of ON_PREMISE (CRM 2016 required) and ONLINE (API version 2016 and higher required).

Custom headers

Add header-value pairs to HTTP requests to achieve specific purposes (for example, to specify the maximum number of pages that can be retrieved at each call). See Compose HTTP requests and handle errors for more information.

To add an header-value pair, add a new line by clicking the plus button under the table and enter the header name and value in the corresponding columns.

This option is available for CRM ON-PREMISE 2016, ONLINE 2016 and 2018.

tStatCatcher Statistics

Select this check box to gather the Job processing metadata at the Job level as well as at each component level.

NB_LINE

The number of rows processed. This is an After variable and it returns an integer.

ERROR_MESSAGE

The error message generated by the component when an error occurs. This is an After variable and it returns a string.

Usage rule

This component is usually used as a start component of a Job or subJob and it always needs an output link.

Limitation

tMicrosoftCrmInput (both ONLINE 2016 and ON-PREMISE 2016) doesn't support retrieving data of the write-only properties. The corresponding schema columns for the write-only properties have been removed from the predefined schema.