Use of 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 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 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 a SSIS warning message.

General page

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

CRM Source Editor

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 have 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 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 a text editor that allows you to enter a snippet of FetchXML query, which is used to retrieve data from the Microsoft Dynamics CRM server.

    CRM Source Editor

    One of the main advantages of the FetchXML option over the Entity option is that you can use FetchXML to build complex queries that retrieves 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 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 above query, if we change the query to the following one, we will not be able to read anything from 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 that 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, 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 column names at design-time.

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

    FetchXML

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

    CRM Source Editor

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

    CRM Source Component - FetchXML Open View

    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 FetchXML query.

    CRM Source Component - SQL2FetchXML Convertor

    Note: Not all SQL queries can be converted due to the limitations of 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 the conversion may complete successfully. For more details about how to work with this convertor, 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. There are also two outputs that come with this option - the CRM Primary Output and the CRM Secondary Output (Deleted records).

    CRM Source Editor

    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 AuditLogs option from the above Source Type option, you will be presented a text editor that allows you to enter a snippet of 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 accountid field, which is the primary key of the account entity (Note that we only use the primary key when reading audit information - from best practice perspective, your query should only return the primary key). You may also include filtering conditions in your query as required.

    CRM Source Editor

    If you want to read all audit records, you can write your FetchXML Query to read from audit 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.

    FetchXML

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

    • Primary Output contains the  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 exist in other outputs.

Impersonate As (since v5.0)

The Impersonate As option is an optional setting in CRM source component, which allows you to specify the CRM user to be impersonated when reading data from 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 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 "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:

CRM Source Component - Refresh Metadata

  • 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 in the General page. 

When the Source Type option has been chosen as Entity in the above General page, you will notice that the grid in 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. 

CRM Source Editor

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.