CDS / Dynamics CRM Source Component

Using the CRM Source Component

The CRM Source Component is an SSIS data flow pipeline component that can be used to read/retrieve data from the Microsoft Dynamics CRM server. The CRM source component supports using a CRM entity or a FetchXML query as the data source. The below documentation can also be used to read/retrieve data from Microsoft Common Data Service (CDS) using the CDS Source component.

The CRM Source Component includes the following two pages to configure how you want to read data from Microsoft Dynamics CRM.

• General
• Columns

The General page allows you to specify the general settings of the CRM source component, while the Columns page shows you all available columns from the entity or FetchXML query. 

Since the v3.0 release, we have added a retry mechanism to the CRM source component, so if an error is encountered when querying the CRM system, the component will make two retries using the same query before giving up. Each retry will be logged as an SSIS warning message.

General Page

The General page of the CRM Source Component allows you to specify the general settings of the component. 

CRM Connection Manager

The CRM source component requires a CRM connection in order to connect with the Microsoft Dynamics CRM server. The CRM Connection Manager option will show all DynamicsCRM connection managers that have been created in the current SSIS package.

Batch Size

The Batch Size option allows you to specify how many records you want to retrieve each time. The default value is 2000. The maximum allowed value is 5000. For performance reasons, this parameter should not be too small. Regardless of the number specified for the Batch Size option, our software will automatically page through the entire data set unless there is a value specified for the Max Rows Returned option as discussed below.

Max Rows Returned (since v9.0)

The Max Rows Returned option allows you to limit the number of rows returned.

• When specified, the CRM source component will stop paging as soon as the exact number of records has been retrieved.
• When the option is set to 0, it will retrieve all satisfying records by automatically paging through the entire data set.

Source Type

The Source Type option allows you to specify whether you want to read data from a CRM entity, use a snippet of FetchXML query, or use the EntityChanges option. Using the Entity option, you have the flexibility of visually picking which fields you want to read from the CRM entity. When FetchXML query is used, you can take advantage of the power and flexibility of CRM FetchXML query, such as JOIN, filtering criteria, etc. Your business requirements should influence which option to choose. The EntityChanges option allows you to make use of the 'Change Tracking' feature in CRM 2015 Update 1 or later to track transactional data changes for CRM entities. The AuditLogs option allows you to retrieve CRM audits and audit details for the entity specified.

Source Type - Entity

The Source Entity option is only available after the Source Type option has been chosen as Entity. When the option is selected, the SSIS Integration Toolkit will retrieve a list of all available CRM entities for the selected CRM connection. Please note that the list will only include the entities where the specified user in the CRM connection manager has read privileges. 

Source Type - FetchXML

When you select FetchXML from the above Source Type option, you will be presented with a text editor that allows you to enter a snippet of the FetchXML query, which is used to retrieve data from the Microsoft Dynamics CRM server.

One of the main advantages of the FetchXML option over the Entity option is that you can use FetchXML to build complex queries that retrieve specific data from the CRM. You can use JOINs in the FetchXML query so that you read data from multiple entities. You can also apply filtering criteria to only retrieve data that meets the criteria, so you only work with the data you are interested in. 

Another big advantage of using the FetchXML option is that you can use FetchXML to query data from virtually any CRM entity. When the Entity option is used, the list of entities available is limited to those that are deemed to be readable according to CRM entity metadata returned by the CRM metadata service.

Note: In order to read from those virtual fields (such as lookup name or OptionSet name fields) using FetchXML, you must include their supporting fields (the lookup field or OptionSet fields). For instance, in the above query, if we change the query to the following one, we will not be able to read anything from the owneridtype field, since the dependent field (ownerid) is missing from the query. The same principle applies to Entity mode as well.

<fetch mapping="logical">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="accountnumber" />
    <attribute name="name" />
    <attribute name="owneridtype" />
    <link-entity name="systemuser" to="ownerid" from="systemuserid" alias="su">
      <attribute name="isdisabled" />
    </link-entity>
  </entity>
</fetch>

Note: When using linked entities in your FetchXML queries, it is recommended that you provide an alias for each linked entity. This can avoid the situation in which the CRM source component returns NULL values for the fields from linked entities. The reason is, in the case that an alias is not assigned to a linked entity, the CRM server could automatically assign a random alias, and the alias name is not entirely predictable, which makes it impractical to generate an accurate list of SSIS columns names at design-time.

Since v1.1, we added support for SSIS variables in the FetchXML query. You can use the toolbar to add variables to your query in order to parameterize the query.

Since v5.2, we have enhanced the support of SSIS variables in the FetchXML query, so you can now use SSIS variables in whichever way you would like to, and you are not limited to using SSIS variables in conditions only.

Since v8.0, we support opening files or CRM views so that you can use the toolbar to open a local file or open a CRM view to get the FetchXML query.

When the Open View option is selected, a 'Select a View' dialog will appear, which can be used to get saved FetchXML queries in CRM. The View dropdown list contains both system views and personal views of the connection user.

We have embedded our SQL2FetchXML converter to the CRM Source component, it supports converting SQL Query into CRM FetchXML query. Click the Convert from SQL button to pop up the convertor. Once you have provided the SQL Query, you can click the Convert button to convert it into a FetchXML query.

Note: Not all SQL queries can be converted due to the limitations of the FetchXML query. Please check the link here for further details of such limitations. In addition, there are no guarantees that the generated FetchXML query will actually work in the source component even if the conversion may complete successfully. For more details about how to work with this converter, please visit the SQL2FetchXML Help Manual.

Source Type - EntityChanges (since v6.0)

This option requires CRM 2015 Update 1 or later, the Change Tracking option needs to be enabled for the entity in Microsoft Dynamics CRM. This option includes two configurable Variables - Input Token Variable and Output Token Variable. Also, two outputs come with this option - the CRM Primary Output and the CRM Secondary Output (Deleted records).

Input Token Variable (Requires 'Change Tracking' feature in CRM 2015 Update 1 to be enabled)

On the initial execution, the value of the variable set may be empty. For this feature to be effective, for any executions after the initial, a variable containing the token retrieved previously from the Output Token Variable should be set to the Input Token Variable.

Output Token Variable (Requires 'Change Tracking' feature in CRM 2015 Update 1 to be enabled)

The token returned by CRM may be written to a variable and stored for later use (ex. to manage incremental changes) by setting it to the Input Token Variable for the next execution.

Source Type - AuditLogs (since v7.0)

When you select the AuditLogs option from the above Source Type option, you will be presented with a text editor that allows you to enter a snippet of the FetchXML query, which is used to retrieve audit data from the Microsoft Dynamics CRM server according to the query specified. For example, if you wanted to retrieve audits from the account entity, you would write your FetchXML query to return the accountid field, which is the primary key of the account entity (Note that we only use the primary key when reading audit information - from a best practice perspective, your query should only return the primary key). You may also include filtering conditions in your query as required.

If you want to read all audit records, you can write your FetchXML Query to read from the audited entity directly. Note that reading all audit records is not typically recommended, and it could take a very long time to complete if you have a large number of audit records.

Note that since our v22.1 release, you can enable the multi-threaded reading option by providing a thread number to scale up your audit data reading performance.

There are five outputs from the CRM Source Component when the AuditLogs option is selected.

• Primary Output contains entity-level audit information.
• Audit Details (Attribute Changes) Output contains the audit details, mainly field-level changes, including the field's old values and new values.
• Audit Details (Relationship Changes) Output contains the audited details of a change in a relationship.
• Audit Details (Record Sharing) Output contains the details for record sharing.
  
• Audit Details (Role Privileges) Output contains the changes to the privilege of a security role.

The Primary Output has a one-to-many relationship to the other outputs, which you can join on the auditid field which exists in other outputs.

Source Type - Metadata (since v22.1)

This option can be used to extract Dynamics 365/CDS and Dataverse metadata from the system. 

This feature provides access to the following system metadata.

Entity Metadata

Using the EntityMetadata option, you will receive a list of all entities in the Dataverse/CDS/CRM system.

Field Metadata

Using the FieldMetadata option, you will receive a list of all entity fields in the Dataverse/CDS/CRM system.

Lookup Metadata

Using the LookupMetadata option, you will receive a list of all lookup fields in the Dataverse/CDS/CRM system, with the following properties returned. We have included a short description of each property below.

• EntityName - this is the entity’s LogicName
• FieldName - this should be the lookup field’s LogicalName
• ReferencedEntity - this is the entity that the lookup can reference to, note that some lookup fields can be multi-targeting, which means that you can expect a lookup field to have multiple rows returned with the ReferencedEntity being different between rows.
• ReferencedEntityPrimaryKeyField – this should be the lookup entity’s primary key field

OptionSet Metadata

Using the OptionSetMetadata option, you will receive a list of all option set values for each OptionSet or picklist field in the Dataverse/CDS/CRM system from all entities, with the following properties returned. We have included a short description of each property below.

• EntityName - this is the entity’s LogicName
• FieldName - this should be the OptionSet/picklist field’s LogicalName
• OptionValue - this contains the integer value of the option
• ParentOptionValue - this contains the integer value of the parent option. This is mainly used for the statuscode field currently by storing its statecode value. Note that this field is not currently populated when using the WebAPI service endpoint.
• IsGlobal - this indicates whether it is a global option set.
• GlobalOptionSetName - this contains the global option set name if the above IsGlobal property is true.
• IsUserLocalizedLabel - this property indicates whether the localized label below is the localized version in the connection user's locale setting.
• LocalizedLabel - the localized label of the option
• LocalizedLabelLanguageCode – the language code of the localized label above.

Impersonate As (since v5.0)

The Impersonate As option is an optional setting in the CRM source component, which allows you to specify the CRM user to be impersonated when reading data from the CRM server. This option should be rarely used and is designed for the scenario where impersonation is required when reading from certain entities. An example is the CRM userquery entity. 

Note that the integration user must have the ActOnBehalfOf privilege or be a member of the PrivUserGroup group in Active Directory in order to use this option.

Output Timezone (since v5.2)

The Output Timezone option specifies how CRM datetime values are produced. There are three options available:

• UTC (Default)
• Adjust to timezone of Connection User
• Adjust to timezone of Impersonation User

Note that the "Adjust to timezone of Impersonation User" option should only be used when Impersonate As option has been set.

Refresh CRM Metadata Button

By clicking this button, the component will retrieve the latest metadata from the CRM server and update each field. Options are available to streamline this process. After clicking the button, the following options will be presented:

• Update all: This would update all the fields regardless of whether there are metadata changes.
• Update if there are incompatible metadata: This option would refresh any incompatible metadata to the discovered metadata.
• Update none: No metadata updates would happen to existing fields.

Columns Page

The Columns page of the CRM Source Component shows you all available columns from the Entity or FetchXML query that you have defined on the General page. 

When the Source Type option has been chosen as Entity on the above General page, you will notice that the grid on the Columns page has a checkbox column on the left which allows you to specify what fields you want to read from the chosen CRM entity. 

On the top left of the grid, you can see a checkbox, which can be used to toggle the selection of all available CRM fields. This is a productive way to check or uncheck all available CRM fields. 

Note: As a general best practice, you should only select the CRM fields that are needed for the downstream pipeline components. This would not only speed up CRM queries but also save resources for the SSIS engine.