Integrating Dynamics 365 (Dataverse) data with SQL Server or Data Warehouses is a common requirement in real-world projects. While OData can be used, FetchXML provides a much more powerful and flexible way to query Dynamics 365 data. In this article, we’ll explore how to use FetchXML in SSIS to load filtered records from Dynamics 365, along with practical examples, paging strategies, and a demo package.
🔹 What is FetchXML?
FetchXML is Microsoft’s proprietary XML-based query language used in Dynamics 365 and Dataverse. It allows developers to:
-
Query specific records and attributes
-
Apply complex filters (AND/OR conditions, date-based filters, null checks)
-
Perform joins between related entities
-
Control sorting and paging
Unlike standard SQL, FetchXML is optimized for the Dynamics 365 API and is supported directly in SSIS adapters such as KingswaySoft and CozyRoc.
🔹 FetchXML Structure
A simple FetchXML query looks like this:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="account">
<attribute name="name" />
<attribute name="accountnumber" />
<order attribute="createdon" descending="true" />
</entity>
</fetch>
Key components:
-
<fetch>– defines query options (version, paging, count) -
<entity>– defines the target table (entity) -
<attribute>– specifies fields to retrieve -
<order>– defines sorting
🔹 Real-World Filter Examples
1. Date Range & Active Records
<filter type="and">
<condition attribute="createdon" operator="on-or-after" value="2025-01-01" />
<condition attribute="createdon" operator="on-or-before" value="2025-06-30" />
<condition attribute="statecode" operator="eq" value="0" />
</filter>
✅ Loads only active accounts created in the first half of 2025.
2. Relative Date & IN Operator
<filter type="and">
<condition attribute="modifiedon" operator="last-x-days" value="90" />
<condition attribute="industrycode" operator="in">
<value>1001</value>
<value>1002</value>
<value>1003</value>
</condition>
</filter>
✅ Retrieves accounts updated in the last 90 days belonging to specific industries.
3. Null Check with OR Condition
<filter type="or">
<condition attribute="telephone1" operator="not-null" />
<condition attribute="emailaddress1" operator="not-null" />
</filter>
✅ Retrieves contacts with at least one communication method available.
🔹 Joins with Link-Entities
FetchXML allows joins between entities using <link-entity>.
Example: Accounts with Primary Contact
<link-entity name="contact" from="contactid" to="primarycontactid" alias="c">
<attribute name="firstname" />
<attribute name="lastname" />
</link-entity>
Example: Accounts with High-Value Opportunities
<link-entity name="opportunity" from="parentaccountid" to="accountid" alias="o">
<attribute name="name" alias="OppName" />
<filter type="and">
<condition attribute="estimatedvalue" operator="gt" value="10000" />
</filter>
</link-entity>
🔹 Paging in FetchXML
Dynamics 365 limits results to 5000 records per request. To fetch more, you need paging.
Example: First Page
<fetch page="1" count="5000">
<entity name="account">
<attribute name="name" />
</entity>
</fetch>
Example: Second Page with Cookie
<fetch page="2" count="5000" paging-cookie="{"cookie":…}">
<entity name="account">
<attribute name="name" />
</entity>
</fetch>
In SSIS with KingswaySoft or CozyRoc, you can simply enable Auto-Paging to handle this automatically.
🔹 SSIS Implementation
Steps to implement FetchXML in SSIS:
-
Create Data Flow Task
-
Add Dynamics 365 Source and configure connection (OAuth/Client Secret)
-
Paste FetchXML query in the FetchXML tab
-
Enable Preview to validate results
-
Enable Auto-Paging (recommended)
-
Add Derived Column / Lookup transformations as needed
-
Connect to an OLE DB Destination (SQL Server or Azure)
-
(Optional) Use Variables + Expressions for dynamic FetchXML queries
🔹 Best Practices
-
Use aliases to avoid column name collisions
-
Always test FetchXML in Preview before running packages
-
Keep FetchXML queries in variables for reusability
-
Adjust page size based on API throttling
-
Use filters to minimize unnecessary data load
🎥 YouTube Tutorial
👉 Watch the full video tutorial here: https://youtu.be/YQ_JZNx0PvU
This video includes live SSIS demo steps, sample FetchXML queries, and real-world integration best practices.
🔹 Conclusion
FetchXML is an essential skill for Dynamics 365 + SSIS developers. It allows you to retrieve exactly the records you need, apply complex filters, join multiple entities, and handle large data volumes with paging. When combined with SSIS, it enables scalable and automated ETL pipelines for Dynamics 365 data.
