July 30, 2023

tMicrosoftCrmInput – Docs for ESB 7.x

  2. Talend for ESB Docs
  3. Docs 7.x
  4. tMicrosoftCrmInput – Docs for ESB 7.x

tMicrosoftCrmInput

Extracts data from a Microsoft CRM database based on conditions set on specific
columns.

tMicrosoftCrmInput Standard properties

These properties are used to configure tMicrosoftCrmInput
running in the Standard Job framework.

The Standard
tMicrosoftCrmInput component belongs to the Business family.

The component in this framework is available in all Talend
products.

Basic settings

Authentication Type
Select the authentication type that corresponds to your specific CRM deployment model.

  • ON_PREMISE: the CRM is installed in-house on the customer’s
    own servers, and licensing is purchased along with any necessary
    infrastructure.

  • ONLINE: the customer accesses the CRM as part of a
    cloud-based subscription service, hosted by Microsoft in their data centers.

CRM Version

Select the version of the ON-PREMISE CRM database, CRM 2011,
CRM 2015, 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.

API Version

Select the API version of the ONLINE CRM database, v2007, v2011,
v2016, 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.

Registered application type

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

This property is available for
Microsoft CRM ONLINE 2016 and 2018.

Microsoft WebService URL

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

This property is available for Microsoft CRM
ON-PREMISE 2011.

OrganizeName

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

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

Username and Password

The user authentication data required to access the Microsoft CRM database.

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

Domain

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

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

Host

The IP address of the Microsoft CRM database server.

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

Port

The listening port number of the Microsoft CRM database server.

This property is available for Microsoft CRM
ON-PREMISE 2011.

Organization Wsdl URL

The WSDL URL of the Organization Service.

This property is available for Microsoft CRM
ON-PREMISE 2015.

Security Service URL

The URL of the Security Token Service.

This property is available for Microsoft CRM
ON-PREMISE 2015.

Trust Certificate

The path to the Trust Certificate file.

This property is available for Microsoft CRM
ON-PREMISE 2015.

Discovery service endpoint

The CRM online discovery service endpoint.

This property is available for Microsoft CRM ONLINE
2011.

Service Root URL

The root URL of the service to be connected.

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

Application ID

The unique client identifier that Azure AD assigns to your application when registering
the application in the Azure portal. For more information, see Register a Dynamics 365 app with Azure Active Directory.

This property is available for
Microsoft CRM ONLINE 2016 and 2018.

Application secret

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

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

OAuth authorization endpoint

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

This property is available for
Microsoft CRM ONLINE 2016 and 2018.

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.
Custom filter

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 property is
available for Microsoft 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
    colulmn 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.
Order by

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 property is
available for Microsoft CRM ON-PREMISE 2016, ONLINE 2016 and 2018.

Advanced settings

Reuse Http Client

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

This property is not available for Microsoft CRM
ON-PREMISE 2015.

Max number of reconnection attempts

The maximum number of reconnect attempts to the Microsoft 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 property is
available for Microsoft CRM ON-PREMISE 2016, ONLINE 2011, 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.

Global Variables

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

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.

Writing data in a Microsoft CRM database and putting conditions on
columns to extract specified rows

This scenario describes a four-component Job which aims at writing the data
included in a delimited input file in a custom entity in a Microsoft CRM database. It then
extracts specified rows to an output file using the conditions set on certain input
columns.

Warning:

If you want to write in a CustomEntity in Microsoft CRM database, make sure
to name the columns in accordance with the naming rule set by Microsoft, that is
“name_columnname” all in lower case.

Setting up the Job

  1. Drop the following components from the Palette to the design workspace: tFileInputDelimited, tFileOutputDelimited, tMicrosoftCrmInput, and tMicrosoftCrmOutput.

    tMicrosoftCrmInput_1.png

  2. Connect tFileInputDelimited to tMicrosoftCrmOutput using a Row > Main
    connection.
  3. Connect tMicrosoftCrmIntput to tFileOutputDelimited using a Row > Main
    connection.
  4. Connect tFileInputDelimited to tMicrosoftCrmInput using Trigger > OnSubjobOk
    connection.

Configuring tFileInputDelimited

  1. Double-click tFileInputDelimited to display
    its Basic settings view and define its
    properties.

    tMicrosoftCrmInput_2.png

  2. Set the Property Type to Repository if you have stored the input file
    properties centrally in the Metadata node in
    the Repository tree view. Otherwise, select
    Built-In and fill the fields that follow
    manually. In this example, property is set to Built-In.
  3. Click the three-dot button next to the File name/Stream
    field and browse to the delimited file that holds the input data. The input file
    in this example contains the following columns: new_id,
    new_status, new_firstname,
    new_email, new_city,
    new_initial and
    new_zipcode.

    tMicrosoftCrmInput_3.png

  4. In the Basic settings view, define the
    Row Separator allowing to identify the end
    of a row. Then define the Field Separator used
    to delimit fields in a row.
  5. If needed, define the header, footer and limit number of processed rows in the
    corresponding fields. In this example, the header, footer and limits are not
    set.
  6. Click Edit schema to open a dialog box where
    you can define the input schema you want to write in Microsoft CRM
    database.

    tMicrosoftCrmInput_4.png

  7. Click OK to close the dialog box.

Configuring tMicrosoftCrmOutput

  1. Double-click tMicrosoftCrmOutput to display
    the component Basic settings view and define
    its properties.

    tMicrosoftCrmInput_5.png

  2. Enter the Microsoft Web Service URL as well as the user name and password in
    the corresponding fields.
  3. In the OrganizeName field, enter the name
    that is given the right to access the Microsoft CRM database.
  4. In the Domain field, enter the domain name of
    the server on which Microsoft CRM is hosted, and then enter the host IP address
    and the listening port number in the corresponding fields.
  5. In the Action list, select the operation you
    want to carry on. In this example, we want to insert data in a custom entity in
    Microsoft Crm.
  6. In the Time out field, set the amount of time
    (in seconds) after which the Job will time out.
  7. In the Entity list, select one among those
    offered. In this example, CustomEntity is selected.

    Note:

    If CustomEntity is selected, a Custom Entity Name field displays where you need to enter a
    name for the custom entity.

    The Schema is then automatically set
    according to the entity selected. If needed, click Edit
    schema     to display a dialog box where you can modify this schema
    and remove the columns that you do not need in the output.
  8. Click Sync columns to retrieve the schema
    from the preceding component.

    tMicrosoftCrmInput_6.png

Configuring tMicrosoftCrmInput

  1. Double-click tMicrosoftCrmInput to display
    the component Basic settings view and define
    its properties.

    tMicrosoftCrmInput_7.png

  2. Set the Property Type to Repository if you have stored the input file
    properties centrally in the Metadata node in
    the Repository tree view. Otherwise, select
    Built-In and fill the fields that follow
    manually. In this example, property is set to Built-In.
  3. Enter the Microsoft Web Service URL as well as the user name and password in
    the corresponding fields and enter the name that is given the right to access
    the Microsoft CRM database in the OrganizeName
    field.
  4. In the Domain field, enter the domain name of
    the server on which Microsoft CRM is hosted, and then enter the host IP address
    and the listening port number in the corresponding fields.
  5. In the Time out field, set the amount of time
    (in seconds) after which the Job will time out.
  6. In the Entity list, select the one among
    those offered you want to connect to. In this example,
    CustomEntity is selected.
  7. The Schema is then automatically set
    according to the entity selected. But you can modify it according to your needs.
    In this example, you should set the schema manually since you want to access a
    custom entity. Copy the seven-column schema from tMicrosoftCrmOutput and paste it in the schema dialog box in
    tMicrosoftCrmInput.

    tMicrosoftCrmInput_8.png

  8. Click OK to close the dialog box. You will be
    prompted to propagate changes. Click Yes in the
    popup message.
  9. In the Basic settings view, select And or Or as the
    logical operator you want to use to combine the conditions you set on the input
    columns. In this example, we want to set two conditions on two different input
    columns and we use And as the logical
    operator.
  10. In the Condition area, click the plus button
    to add as many lines as needed and then click in each line in the Input column list and select the column you want to
    set condition on. In this example, we want to set conditions on two columns,
    new_city and new_id. We want to
    extract all customer rows whose city is equal to “New York” and whose id is
    greater than 2.
  11. Click in each line in the Operator list and
    select the operator to bind the input column with its value, in this example
    Equal is selected for
    new_city and Greater
    Than     for new_id.
  12. Click in each line in the Value list and set
    the column value, New York for new_city and
    2 for new_id in this example. You can use
    a fixed or a context value in this field.

Configuring tFileOutputDelimited

  1. Double-click tFileOutputdelimited to display
    the component Basic settings view and define
    its properties.

    tMicrosoftCrmInput_9.png

  2. Set Property Type to Built-In and then click the […] button next to the File
    Name     field and browse to the output file.
  3. Set row and field separators in the corresponding fields.
  4. Select the Append check box if you want to
    add the new rows at the end of the records.
  5. Select the Include Header check box if the
    output file includes a header.
  6. Click Sync columns to retrieve the schema
    from the preceding component.

Executing the Job

Save the Job and press F6 to execute it.
tMicrosoftCrmInput_10.png

Only customers who live in New York city and those whose “id” is
greater than 2 are listed in the output file you stored locally.

Document get from Talend https://help.talend.com
Thank you for watching.
Docs 7.x
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x