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

Max Rows Returned(since v9.0)

The Max Rows Returend option allows you to limit the number of rows returned. When the value is 0, it will retrieve all satisfying records by automatically paging through the results using the provided Batch Size.

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 use, and you are not limited to use SSIS variables in conditions only.

CRM Source Editor

Since v8.0, we support of opening files or CRM views so that you can use the toolbar to open 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

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 two outputs from the CRM Source Component when the AuditLogs option is selected. The Primary Output contains the  entity-level audit information, while the Secondary Output contains the audit details, mainly field-level changes, including the field's old values and new values. The Primary Output has a one-to-many relationship to the Secondary Output, which you can join on the auditid field which exist in both 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. This feature works by performing the following three actions.

  • Update any existing fields to the latest CRM metadata
  • Add any new CRM fields that have recently been created in CRM system
  • Remove any CRM fields that have recently been deleted from CRM system

After clicking this button, you will receive the following screen once the refresh is done.

Refresh Metadata

The Refresh CRM Metadata feature has been designed to minimize any rework after the refresh. Particularly, the component will only update any existing fields to the latest CRM metadata without actually deleting and re-creating them, which would otherwise cause rework if you have downstream pipeline component mapped to such fields.

This button can be also useful if you want to change the source component's Connection Manager, or even Source Entity/FetchXML option without having to re-configure the component. In case you need to do so, you can first use the source component's Advanced Editor window to make the changes accordingly, then open the source component using its standard editor window, and click the "Refresh CRM Metadata" button, which should update the component properly.

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.