SSIS Dynamics CRM / Dataverse - Read / Import data into SQL Server

Today Microsoft Dynamics CRM / Dataverse is one of the most popular CRMs in the world. SSIS Dynamics CRM / Dataverse read operation can be achieved using Dynamics CRM / Dataverse Source Connector. It allows connecting to Dynamics CRM / Dataverse 365 Online or On-Premises Installation / Hosted CRM instance (IFD – internet facing deployment). You can read data from Dynamics CRM / Dataverse using SSIS connector and then exports to other sources (e.g. SQL Server, Oracle or Flat File).

In this new article, we will show how to connect and download the Dynamics CRM / Dataverse 365 (Online) data using Dynamics CRM / Dataverse Source Connector, how to query CRM data using FetchXML in SSIS and how to use variables in CRM FetchXML Query.

Requirements for SSIS Dynamics CRM / Dataverse read component

First, you will need SSDT installed
Second, You will need to install ZappySys SSIS PowerPack which includes an SSIS Dynamics CRM / Dataverse Source task.
Finally, a Microsoft Dynamics account

SSIS Dynamics CRM / Dataverse Source – Video Tutorial

[youtube https://www.youtube.com/watch?v=zl0ecdjuLGk?rel=0&showinfo=0&w=560&h=315]

Getting started with SSIS Dynamics CRM / Dataverse read component

Register Dynamics CRM / Dataverse App obtain App Id / Client Secret

The very first step to access Microsoft Dynamics CRM / Dataverse 365 API is to obtain Application Id (also referred as Client Id) and Client Secret. We will use this information later in this article (on OAuth connection UI).
Register Dynamics CRM / Dataverse App With Azure and get the credential (App Id, Client Secret).

Configure Dynamics CRM / Dataverse Connection

In the connection manager, press the new button to create a new connection:

SSIS create a new connection CRM Dynamics
Select the ZS-DYNAMICS-CRM Connection. We will use a CRM Online connection, username, password, and organization:

NOTE: Please check the Discovery and ORG Service URL are the same as your organization URL. And if you are using Customize URL make sure that you are using it in both Discovery URL as well as in ORG Service URL also.

SSIS Dynamics CRM / Dataverse credentials

How to create a Dynamics CRM / Dataverse Source

In the control flow, drag and drop the data flow:
Drag and drop the ZS Dynamics CRM / Dataverse Source:

SSIS Dynamics CRM / Dataverse source
Second, select the connection created before and select the account table:
SSIS entity table account

Configure SQL Server Destination

In the following example, we will show how to export the data from the account entity to SQL Server.

First, drag and drop the OLEDB Destination and join with our ZS CRM Dynamics source:

SSIS source dynamics SQL destination
Make sure to select the account table from Dynamics:

SSIS Dynamics CRM / Dataverse read entity table account

In addition, select an SQL Server and a database. In the name of the table, we will create a table with the following columns:

CREATE TABLE [crm dynamics] (
[accountcategorycode] nvarchar(255),

[accountid] uniqueidentifier,
[accountnumber] nvarchar(20),
[accountratingcode] nvarchar(255),
[accountratingcodename] nvarchar(255),
[address1_addressid] uniqueidentifier,
[address1_city] nvarchar(80),

)

CREATE TABLE [crm dynamics] (

[accountcategorycode] nvarchar(255),

[accountid] uniqueidentifier,

[accountnumber] nvarchar(20),

[accountratingcode] nvarchar(255),

[accountratingcodename] nvarchar(255),

[address1_addressid] uniqueidentifier,

[address1_city] nvarchar(80),

)

SSIS OLEBD SQL Server

Go to the mapping page to map all the source and destination columns:

SSIS Dynamics CRM / Dataverse read mapping

Load Dynamics CRM / Dataverse Data to SQL Server

Execute the package:

SSIS start package
Finally, you can see the data exported in SQL Server. Select the data from the CRM Dynamics table:

Data exported from SSIS Dynamic CRM read

How to query Dynamics CRM / Dataverse

You can query Dynamics CRM / Dataverse data in two modes (Table mode or Query Mode). To use query mode you have to use a special language called FetchXML (sorry no SQL support). FetchXML is an XML based query language which can be used to SELECT, FILTER, Aggregate, Transform data from CRM. Not all features from traditional SQL Language Found in FetchXML but basic query features can be implemented by writing FetchXML. Here is an example of typical FetchXML.

<fetch mapping="logical" version="1.0">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="country" />
    <filter>
      <condition attribute="country" operator="in">
        <value>USA</value>
        <value>UK</value>
        <value>INDIA</value>
      </condition>
      <condition attribute="status" operator="like" value="ACT%" />
    </filter>
  </entity>
</fetch>

<value>INDIA</value>

</condition>

</filter>

</entity>

</fetch>

Above FetchXML query is equivalent to below SQL query in traditional RDBMS.

SELECT accountid,name,country 
FROM account
WHERE country IN ('USA','UK','INDIA') 
AND status LIKE 'ACT%'

SELECT accountid,name,country

FROM account

WHERE country IN ('USA','UK','INDIA')

AND status LIKE 'ACT%'

Now let’s look at how to build FetchXML queries by UI or by Hand.

How to generate a FetchXML query using UI

The Dynamics CRM / Dataverse portal contains an option to create queries visually that can be exported to XML. To do that use the Advanced Find option in the portal:

Dynamics CRM / Dataverse fetch XML
For example, we will query the Accounts entity where the account name is A. Datum:

dynamics 365 CRM Create a query
Next, we can download the Fetch XML:

Dynamics CRM / Dataverse xml download

Let’s check some examples about fetch XML:

SELECT All Columns (*)

This example shows how to write a simple FetchXML query to select all columns
of the account entity.

This FetchXML is the equivalent of below SQL

SELECT * FROM account

1	SELECT * FROM account

The option all-attributes show all the attributes. In this case, of the account entity.

To build custom FetxhXML:
1. Login to your Dynamics CRM / Dataverse Portal
2. Click on Advanced Find icon (Found next to the search textbox)
3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM / Dataverse Source.

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
 <entity name='account'>
 <all-attributes />
 </entity>  
 </fetch>

<all-attributes />

</entity>

</fetch>

SELECT Specified Columns and Alias with SSIS Dynamics CRM / Dataverse

This example shows how to write a simple FetchXML query to select two columns and use an alias for column name (e.g. name is renamed to accountname).

This FetchXML is the equivalent of below SQL

SELECT accountid, name as accountname
FROM account

1 2	SELECT accountid, name as accountname FROM account

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical'> 
	<entity name='account'>
		<attribute name='accountid'/> 
		<attribute name='name' alias = 'accountname'/> 
	</entity>
</fetch>

</entity>

</fetch>

WHERE (AND conditions)

This example shows how to write FetchXML query to filter data using multiple conditions (AND).

This FetchXML is the equivalent of below SQL

SELECT * FROM account WHERE revenue > 0 AND name like 'A%'

1	SELECT * FROM account WHERE revenue > 0 AND name like 'A%'

To build custom FetxhXML
1. Login to your Dynamics CRM / Dataverse Portal
2. Click on Advanced Find icon (Found next to the search textbox)
3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM / Dataverse Source.
For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
	<entity name='account'>
		<all-attributes />
		<filter>
			<condition attribute='revenue' operator='gt' value='0' />
			<condition attribute='name' operator='like' value='A%' />
		</filter>
	</entity>
</fetch>

<all-attributes />

</filter>

</entity>

</fetch>

WHERE (OR conditions)

This example shows how to write FetchXML query to filter data using multiple conditions (OR).

This FetchXML is the equivalent of below SQL

SELECT * FROM account WHERE revenue > 0 OR name like 'A%'

1	SELECT * FROM account WHERE revenue > 0 OR name like 'A%'

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
	<entity name='account'>
		<all-attributes />
		<filter type='or'>
			<condition attribute='revenue' operator='gt' value='0' />
			<condition attribute='name' operator='like' value='A%' />
		</filter>
	</entity>
</fetch>

<all-attributes />

</filter>

</entity>

</fetch>

WHERE (Mixing AND / OR conditions)

This example shows how to write FetchXML query to filter data using multiple conditions (Mixing AND / OR).

This FetchXML is the equivalent of below SQL

SELECT * FROM account 
WHERE revenue > 0 AND
    (
     name LIKE 'A%' OR 
     name LIKE 'B%'
    )

SELECT * FROM account

WHERE revenue > 0 AND

(

name LIKE 'A%' OR

name LIKE 'B%'

)

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
	<entity name='account'>
		<all-attributes />
		<filter>
			<condition attribute='revenue' operator='gt' value='0' />
			<filter type='or'>
				<condition attribute='name' operator='like' value='A%' />
				<condition attribute='name' operator='like' value='B%' />
			</filter>
		</filter>
	</entity>
</fetch>

<all-attributes />

</filter>

</entity>

</fetch>

SELECT DISTINCT (Exclude duplicates)

This example shows how to write FetchXML query to get distinct records (exclude duplicates).

This FetchXML is the equivalent of below SQL

SELECT DISTINCT accountid,name FROM account

1	SELECT DISTINCT accountid,name FROM account

For more information about FetchXML query syntax check below links:
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' distinct='true' version='1.0'>
	<entity name='account'> 
		<attribute name='accountid' />
		<attribute name='name' />
	</entity>
</fetch>

</entity>

</fetch>

SELECT TOP using SSIS Dynamics CRM / Dataverse read

This example shows how to write FetchXML query to select TOP N rows. This setting may not work if you using preview mode.

This FetchXML is the equivalent of below SQL

SELECT TOP 10 * FROM account

1	SELECT TOP 10 * FROM account

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' count='10' version='1.0'>
  <entity name='account'>
    <all-attributes />
  </entity>
</fetch>

<all-attributes />

</entity>

</fetch>

GROUP BY / Aggregate functions

This example shows how to use aggregate functions such as SUM, MIN, MAX, AVG along with GROUP BY clause. When you use the Aggregate function in SQL Query you can also use an alias for the field. Refer FetchXML help to learn more about aggregate functions.

This FetchXML is the equivalent of below SQL

SELECT AVG(estimatedvalue) AS estimatedvalue_avg, COUNT(name) AS opportunity_count, ownerid AS ownerid
FROM opportunity 
GROUP BY ownerid

SELECT AVG(estimatedvalue) AS estimatedvalue_avg, COUNT(name) AS opportunity_count, ownerid AS ownerid

FROM opportunity

GROUP BY ownerid

You can use COUNT, COUNT(attribute_name), SUM, MIN, MAX, AVG as an aggregate function

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='opportunity'> 
       <attribute name='estimatedvalue' aggregate='avg' alias='estimatedvalue_avg'  /> 
       <attribute name='name' aggregate='countcolumn' alias='opportunity_count' /> 
       <attribute name='ownerid' alias='ownerid' groupby='true' /> 
    </entity> 
</fetch>

</entity>

</fetch>

INNER JOIN (matching child records)

This example shows how to write FetchXML query to get account and associated contacts where email is @gmail.com or yahoo.com or hotmail.com and revenue for account is > 10000.

This FetchXML is the equivalent of below SQL

SELECT a.name, c.firstname, c.lastname, c.email
FROM account a
INNER JOIN contact c ON a.accountid=c.parentcustomerid
                     AND (c.email LIKE '%@yahoo.com'
                          OR c.email LIKE '%@gmail.com'
                          OR c.email LIKE '%@hotmail.com')
WHERE a.revenue > 10000

SELECT a.name, c.firstname, c.lastname, c.email

FROM account a

INNER JOIN contact c ON a.accountid=c.parentcustomerid

AND (c.email LIKE '%@yahoo.com'

OR c.email LIKE '%@gmail.com'

OR c.email LIKE '%@hotmail.com')

WHERE a.revenue > 10000

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='revenue' operator='gt' value='10000' />
    </filter>
    <link-entity name='contact' from='parentcustomerid' to='accountid' alias='c' link-type='inner'>
      <attribute name='firstname' />
      <attribute name='lastname' />
      <attribute name='email' />
      <filter type='or'>
        <condition attribute='email' operator='like' value='%@yahoo.com' />
        <filter type='or'>
          <condition attribute='email' operator='like' value='%@gmail.com' />
          <condition attribute='email' operator='like' value='%@hotmail.com' />
        </filter>
      </filter>
    </link-entity>
  </entity>
</fetch>

</filter>

</filter>

</link-entity>

</entity>

</fetch>

LEFT OUTER JOIN (‘not in’ type query)

This example shows how to write FetchXML query to find all leads that have no tasks, using an alias.

This FetchXML is the equivalent of below SQL

SELECT lead.FullName
FROM Leads as lead
LEFT OUTER JOIN Tasks as ab
    ON (lead.leadId  =  ab.RegardingObjectId)
WHERE ab.RegardingObjectId is null

SELECT lead.FullName

FROM Leads as lead

LEFT OUTER JOIN Tasks as ab

ON (lead.leadId = ab.RegardingObjectId)

WHERE ab.RegardingObjectId is null

For more information on left outer join syntax check below link
https://msdn.microsoft.com/en-us/library/dn531006.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
  <entity name='lead'>
    <attribute name='fullname' />
    <link-entity name='task' from='regardingobjectid' to='leadid' alias='ab' link-type='outer'>
       <attribute name='regardingobjectid' />
    </link-entity>
    <filter type='and'>
        <condition entityname='ab' attribute='regardingobjectid' operator='null' />
    </filter>
  </entity>
<fetch/>

</link-entity>

</filter>

</entity>

ORDER BY

This example shows how to write FetchXML query with order by specified for one or more attributes.

This FetchXML is the equivalent of below SQL

SELECT firstname, lastname, email FROM contact
ORDER BY lastname ASC, firstname DESC

1 2	SELECT firstname, lastname, email FROM contact ORDER BY lastname ASC, firstname DESC

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
  <entity name='contact'>
    <attribute name='firstname' />
    <attribute name='lastname' />
    <attribute name='email' />
    <order attribute='lastname' />
    <order attribute='firstname' descending='true' />
  </entity>
</fetch>

</entity>

</fetch>

OPERATOR – Equal ( = )

This example shows how to write FetchXML query using equal operator.

This FetchXML is the equivalent of below SQL

SELECT name FROM account WHERE revenue=0

1	SELECT name FROM account WHERE revenue=0

For a list of supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

 <fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='revenue' operator='eq' value='0' />
    </filter>
  </entity>
</fetch

</filter>

</entity>

</fetch

OPERATOR – Not Equal ( != )

This example shows how to write FetchXML query using not equal operator.

This FetchXML is the equivalent of below SQL

SELECT name FROM account WHERE revenue != 0

1	SELECT name FROM account WHERE revenue != 0

For a list of supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='revenue' operator='ne' value='0' />
    </filter>
  </entity>
</fetch>

</filter>

</entity>

</fetch>

OPERATOR – Greater Than ( > )

This example shows how to write FetchXML query using greater than the operator.

This FetchXML is the equivalent of below SQL

SELECT name FROM account WHERE revenue > 0

1	SELECT name FROM account WHERE revenue > 0

For a list of supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='revenue' operator='gt' value='0' />
    </filter>
  </entity>
</fetch>

</filter>

</entity>

</fetch>

OPERATOR – Less Than ( < )

This example shows how to write FetchXML query using greater than the operator.

This FetchXML is the equivalent of below SQL

SELECT name FROM account WHERE revenue < 0

1	SELECT name FROM account WHERE revenue < 0

For a list of supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

 <fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='revenue' operator='lt' value='0' />
    </filter>
  </entity>
</fetch>

</filter>

</entity>

</fetch>

OPERATOR – IN (Check for multiple matching values)

This example shows how to write FetchXML query to find multiple matching values using IN operator.

This FetchXML is the equivalent of below SQL

SELECT name FROM account WHERE name IN ('Microsoft', 'Yahoo', 'Google')

1	SELECT name FROM account WHERE name IN ('Microsoft', 'Yahoo', 'Google')

For a list of supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='name' operator='in'>
        <value>Microsoft</value>
        <value>Yahoo</value>
        <value>Google</value>
      </condition>
    </filter>
  </entity>
</fetch>

<value>Microsoft</value>

<value>Yahoo</value>

<value>Google</value>

</condition>

</filter>

</entity>

</fetch>

OPERATOR – NULL (Check for NULL values)

This example shows how to write FetchXML query to find NULL values (search for records where email not specified).

This FetchXML is the equivalent of below SQL

SELECT firstname, lastname, email FROM contact WHERE email is NULL

1	SELECT firstname, lastname, email FROM contact WHERE email is NULL

For a list of supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

 <fetch mapping='logical' version='1.0'>
  <entity name='contact'>
    <attribute name='firstname' />
    <attribute name='lastname' />
    <attribute name='email' />	
    <filter>
      <condition attribute='email' operator='null' />
    </filter>
  </entity>
</fetch>

</filter>

</entity>

</fetch>

OPERATOR – NOT NULL (Check for NOT NULL values)

This example shows how to write FetchXML query to find NOT NULL values (search for records where email not specified).

This FetchXML is the equivalent of below SQL

SELECT firstname, lastname, email FROM contact WHERE email is NOT NULL

1	SELECT firstname, lastname, email FROM contact WHERE email is NOT NULL

For a list of supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
  <entity name='contact'>
    <attribute name='firstname' />
    <attribute name='lastname' />
    <attribute name='email' />	
    <filter>
      <condition attribute='email' operator='not-null' />
    </filter>
  </entity>
</fetch>

</filter>

</entity>

</fetch>

OPERATOR – LIKE (Pattern search)

This example shows how to write FetchXML query using LIKE operator to match string by pattern search.

This FetchXML is the equivalent of below SQL

SELECT name FROM account WHERE name LIKE 'zappy%'

1	SELECT name FROM account WHERE name LIKE 'zappy%'

For a list of supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='name' operator='like' value='zappy%' />
    </filter>
  </entity>
</fetch>

</filter>

</entity>

</fetch>

OPERATOR – NOT LIKE (Pattern search)

This example shows how to write FetchXML query using LIKE operator to match string by pattern search.

This FetchXML is the equivalent of below SQL

SELECT name FROM account WHERE name NOT LIKE 'zappy%'

1	SELECT name FROM account WHERE name NOT LIKE 'zappy%'

For a list of supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

  <fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='name' operator='not-like' value='zappy%' />
    </filter>
  </entity>
</fetch>

</filter>

</entity>

</fetch>

OPERATOR – UNDER (Find all children for given hierarchy) with the SSIS Dynamics CRM / Dataverse read task

This example shows how to write FetchXML query to get children count for a given hierarchy.

For more information about querying hierarchical data check below link
https://msdn.microsoft.com/en-us/library/dn817893.aspx

For a list of supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch distinct='false' no-lock='false' mapping='logical'>
  <entity name='account'>
    <attribute name='name' />
    <attribute name='accountid' />
    <attribute name='accountid' rowaggregate='CountChildren' alias='AccountChildren'/>
    <filter type='and'>
      <condition attribute='accountid' operator='under' value='{0}' />
    </filter>
  </entity>
</fetch>

</filter>

</entity>

</fetch>

Using DateTime Operators with the SSIS Dynamics CRM / Dataverse read task

This example shows how to write FetchXML query to get all tickets which are created 30 mins before.

Other similar operators are
olderthan-x-hours
olderthan-x-days
olderthan-x-weeks
olderthan-x-months
olderthan-x-years

For a list of supported DateTime operators
https://msdn.microsoft.com/en-us/library/gg334216.aspx

For a list of all supported operators check below XSD and search for name=”operator”
https://msdn.microsoft.com/en-us/library/gg309405.aspx

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

 <fetch>
  <entity name='incident'>
    <attribute name='title' />
    <attribute name='ticketnumber' />
    <attribute name='createdon' />
    <attribute name='incidentid' />
    <filter type='and'>
      <condition attribute='createdon' operator='olderthan-x-minutes' value='30' />
    </filter>
  </entity>
</fetch>

<fetch>

</filter>

</entity>

</fetch>

Query – All activities (sort, outer join, alias) with the SSIS Dynamics CRM / Dataverse read task

This example shows how to get all activities performed on lead or contact. It shows how to get attributes from lead and contact by using OUTER JOIN.

This FetchXML is the equivalent of below SQL

SELECT a.*
, l.firstname as lead_name,l.emailadddress1 as lead_email1
, c.firstname as contact_name
FROM activitypointer a
LEFT OUTER JONI lead l ON a.regardingobjectid=l.leadid
LEFT OUTER JONI lead c ON a.regardingobjectid=c.contactid
ORDER BY a.createdon DESC

SELECT a.*

, l.firstname as lead_name,l.emailadddress1 as lead_email1

, c.firstname as contact_name

FROM activitypointer a

LEFT OUTER JONI lead l ON a.regardingobjectid=l.leadid

LEFT OUTER JONI lead c ON a.regardingobjectid=c.contactid

ORDER BY a.createdon DESC

For more information about FetchXML query syntax check below links :
https://msdn.microsoft.com/en-us/library/gg328117.aspx
https://msdn.microsoft.com/en-us/library/gg328332.aspx

For complete syntax and validation rule for FetchXML check below XSD
https://msdn.microsoft.com/en-us/library/gg309405.aspx

<fetch version='1.0' mapping='logical'>
	<entity name='activitypointer'>
		<attribute name='subject' />
		<attribute name='ownerid' />
		<attribute name='regardingobjectid' />
		<attribute name='activitytypecode' />
		<attribute name='scheduledstart' />
		<attribute name='scheduledend' />
		<attribute name='instancetypecode' />
		<attribute name='community' />
		<attribute name='actualend' />
		<attribute name='createdon' />
		
		<link-entity name='lead' from='leadid' to='regardingobjectid' link-type='outer' alias='l'>
			<attribute name='firstname' alias='lead_name' />
			<attribute name='emailaddress1' alias='lead_email1' />
			<attribute name='companyname' alias='lead_company' />
		</link-entity>  
		
		<link-entity name='contact' from='contactid' to='regardingobjectid' link-type='outer' alias='c'>
			<attribute name='firstname' alias='contact_name' />
		</link-entity>  

		<order attribute='createdon' descending='true' />
	</entity>
</fetch>

</link-entity>

</link-entity>

</entity>

</fetch>

How to use SSIS variables with Fetch XML in SSIS for SSIS Dynamics

CRM read

You can include variables in an XML fetch.

First, create your variable in the SSDT. Go to SSIS and Variables and create your variable:

SSIS variable value

In addition, create your fetch, include the variable in the XM ({{User::value}}):

<fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='revenue' operator='eq' value='{{User::value}}' />
    </filter>
  </entity>
</fetch>

</filter>

</entity>

</fetch>

Conclusion about the SSIS Dynamics CRM / Dataverse read task

To conclude, we can say that the ZS Dynamic CRM source is a great tool to get data from Microsoft CRM Dynamics and export any other destination format. In this article, we learn how to create queries to CRM Dynamics using fetch data. We used several examples to query data. We also learned to use SSIS variables in our queries and how to export the data from Dynamics CRM / Dataverse to SQL Server. Download SSIS PowerPack to explore Dynamics CRM / Dataverse integration possibilities in SSIS.