{"id":3504,"date":"2018-05-15T20:36:40","date_gmt":"2018-05-15T20:36:40","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=3504"},"modified":"2025-09-19T12:52:33","modified_gmt":"2025-09-19T12:52:33","slug":"ssis-dynamics-crm-read-import-crm-365-onpremises","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/ssis-dynamics-crm-read-import-crm-365-onpremises\/","title":{"rendered":"SSIS Dynamics CRM \/ Dataverse – Read \/ Import data into SQL Server"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>Today Microsoft Dynamics CRM \/ Dataverse (Dynamics 365 \/ On-Premises CRM \/ PowerApp) is one of the most popular CRMs in the world. SSIS Dynamics CRM \/ Dataverse (Dynamics 365 \/ On-Premises CRM \/ PowerApp) read operation can be achieved using Dynamics CRM \/ Dataverse Source Connector<\/a>. It allows connecting to Dynamics CRM \/ Dataverse 365 Online<\/strong> or On-Premises Installation<\/strong> \/ Hosted<\/strong> 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).<\/p>\n

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<\/a>, how to query CRM data using FetchXML<\/strong> in SSIS and how to use variables in CRM FetchXML Query.<\/p>\n

 <\/p>\n

Requirements for SSIS Dynamics CRM \/ Dataverse (Dynamics 365 \/ On-Premises CRM \/ PowerApp) read component<\/h2>\n
    \n
  1. First, you will need SSDT installed<\/li>\n
  2. Second, You will need to install ZappySys SSIS PowerPack<\/a> which includes an SSIS Dynamics CRM \/ Dataverse Source (Dynamics 365 \/ On-Premises CRM \/ PowerApp) task.<\/li>\n
  3. Finally, a Microsoft Dynamics account<\/li>\n<\/ol>\n

    SSIS Dynamics CRM \/ Dataverse Source – Video Tutorial<\/h2>\n[youtube https:\/\/www.youtube.com\/watch?v=zl0ecdjuLGk?rel=0&showinfo=0&w=560&h=315]\n

    Getting started with\u00a0SSIS Dynamics CRM \/ Dataverse read component<\/h2>\n

    Register Dynamics CRM \/ Dataverse App obtain App Id \/ Client Secret<\/h3>\n

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

    Configure Dynamics CRM \/ Dataverse Connection<\/h3>\n
      \n
    1. \n In the connection manager, press the new button to create a new connection:<\/p>\n

    2. \n Select the ZS-DYNAMICS-CRM Connection.\u00a0 We will use a CRM Online connection, username, password, and organization:
      \n
      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\u00a0also.<\/div><\/div>\n

      \"SSIS<\/a>

      SSIS Dynamics CRM \/ Dataverse credentials<\/p><\/div>\n <\/li>\n<\/ol>\n

      How to create a Dynamics CRM \/ Dataverse Source<\/h3>\n
        \n
      1. In the control flow, drag and drop the data flow:<\/li>\n
      2. \n Drag and drop the ZS Dynamics CRM \/ Dataverse Source:<\/p>\n

      3. \n Second, select the connection created before and select the account table:<\/p>\n
        \"ssis<\/a>

        SSIS entity table account<\/p><\/div>\n

         \n <\/li>\n<\/ol>\n

        Configure SQL Server Destination<\/h3>\n

        In the following example, we will show how to export the data from the account entity to SQL Server.<\/p>\n

          \n
        1. \n First, drag and drop the OLEDB Destination and join with our ZS CRM Dynamics source:<\/p>\n

        2. \n Make sure to select the account table from Dynamics:<\/p>\n

        3. \n In addition, select an SQL Server and a database. In the name of the table, we will create a table with the following columns:<\/p>\n
          CREATE TABLE [crm dynamics] (\r\n[accountcategorycode] nvarchar(255),\r\n\r\n[accountid] uniqueidentifier,\r\n[accountnumber] nvarchar(20),\r\n[accountratingcode] nvarchar(255),\r\n[accountratingcodename] nvarchar(255),\r\n[address1_addressid] uniqueidentifier,\r\n[address1_city] nvarchar(80),\r\n\r\n)<\/pre>\n
                  
          \"ssis<\/a>
          SSIS OLEBD SQL Server<\/p><\/div>\n    <\/li>\n
        4. \n        Go to the mapping page to map all the source and destination columns:<\/p>\n
                  
          \"dynamics<\/a>
          SSIS Dynamics CRM \/ Dataverse read mapping<\/p><\/div>\n    <\/li>\n<\/ol>\n
          Load Dynamics CRM \/ Dataverse Data to SQL Server<\/h3>\n
            \n
          1. \n        Execute the package:<\/p>\n
                    
          2. Finally, you can see the data exported in SQL Server. Select the data from the CRM Dynamics table:<\/li>\n<\/ol>\n
            \"Data<\/a>
            Data exported from SSIS Dynamic CRM read<\/p><\/div>\n
            How to query Dynamics CRM \/ Dataverse<\/h2>\n
            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<\/a> (sorry no SQL support).\u00a0 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.<\/p>\n
            <fetch mapping=\"logical\" version=\"1.0\">\r\n  <entity name=\"account\">\r\n    <attribute name=\"accountid\" \/>\r\n    <attribute name=\"name\" \/>\r\n    <attribute name=\"country\" \/>\r\n    <filter>\r\n      <condition attribute=\"country\" operator=\"in\">\r\n        <value>USA<\/value>\r\n        <value>UK<\/value>\r\n        <value>INDIA<\/value>\r\n      <\/condition>\r\n      <condition attribute=\"status\" operator=\"like\" value=\"ACT%\" \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch><\/pre>\n
            Above FetchXML query is equivalent to below SQL query in traditional RDBMS.<\/p>\n
            SELECT accountid,name,country \r\nFROM account\r\nWHERE country IN ('USA','UK','INDIA') \r\nAND status LIKE 'ACT%'<\/pre>\n
             <\/p>\n
            Now let’s look at how to build FetchXML queries by UI or by Hand.<\/p>\n
             <\/p>\n
            How to generate a FetchXML query using UI<\/h3>\n
              \n
            1. \n        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<\/strong> option in the portal:<\/p>\n
                      
            2. \n        For example, we will query the Accounts entity where the account name is A. Datum:<\/p>\n
                      
            3. \n        Next, we can download the Fetch XML:<\/p>\n
                      
              \"Dynamics<\/a>
              Dynamics CRM \/ Dataverse xml download<\/p><\/div>\n    <\/li>\n<\/ol>\n
              Let\u2019s check some examples about fetch XML:<\/p>\n
              SELECT All Columns (*)<\/h3>\n
              This example shows how to write a simple FetchXML query to select all columns
              \nof the account entity.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT * FROM account<\/pre>\n
              The option all-attributes show all the attributes. In this case, of the account entity.<\/p>\n
              To build custom FetxhXML:
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' version='1.0'>\r\n <entity name='account'>\r\n <all-attributes \/>\r\n <\/entity>  \r\n <\/fetch>   \r\n  \r\n<\/pre>\n
              SELECT Specified Columns and Alias with\u00a0SSIS Dynamics CRM \/ Dataverse<\/h3>\n
              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).<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT accountid, name as accountname\r\nFROM account<\/pre>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical'> \r\n\t<entity name='account'>\r\n\t\t<attribute name='accountid'\/> \r\n\t\t<attribute name='name' alias = 'accountname'\/> \r\n\t<\/entity>\r\n<\/fetch><\/pre>\n
              WHERE (AND conditions)<\/h3>\n
              This example shows how to write FetchXML query to filter data using multiple conditions (AND).<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT * FROM account WHERE revenue > 0 AND name like 'A%'<\/pre>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.
              \nFor more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
                     \r\n<fetch mapping='logical' version='1.0'>\r\n\t<entity name='account'>\r\n\t\t<all-attributes \/>\r\n\t\t<filter>\r\n\t\t\t<condition attribute='revenue' operator='gt' value='0' \/>\r\n\t\t\t<condition attribute='name' operator='like' value='A%' \/>\r\n\t\t<\/filter>\r\n\t<\/entity>\r\n<\/fetch><\/pre>\n
              WHERE (OR conditions)<\/h3>\n
              This example shows how to write FetchXML query to filter data using multiple conditions (OR).<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT * FROM account WHERE revenue > 0 OR name like 'A%'<\/pre>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.
              \nFor more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' version='1.0'>\r\n\t<entity name='account'>\r\n\t\t<all-attributes \/>\r\n\t\t<filter type='or'>\r\n\t\t\t<condition attribute='revenue' operator='gt' value='0' \/>\r\n\t\t\t<condition attribute='name' operator='like' value='A%' \/>\r\n\t\t<\/filter>\r\n\t<\/entity>\r\n<\/fetch><\/pre>\n
              WHERE (Mixing AND \/ OR conditions)<\/h3>\n
              This example shows how to write FetchXML query to filter data using multiple conditions (Mixing AND \/ OR).<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT * FROM account \r\nWHERE revenue > 0 AND\r\n    (\r\n     name LIKE 'A%' OR \r\n     name LIKE 'B%'\r\n    )<\/pre>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.
              \nFor more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
                  \r\n<fetch mapping='logical' version='1.0'>\r\n\t<entity name='account'>\r\n\t\t<all-attributes \/>\r\n\t\t<filter>\r\n\t\t\t<condition attribute='revenue' operator='gt' value='0' \/>\r\n\t\t\t<filter type='or'>\r\n\t\t\t\t<condition attribute='name' operator='like' value='A%' \/>\r\n\t\t\t\t<condition attribute='name' operator='like' value='B%' \/>\r\n\t\t\t<\/filter>\r\n\t\t<\/filter>\r\n\t<\/entity>\r\n<\/fetch><\/pre>\n
              SELECT DISTINCT (Exclude duplicates)<\/h3>\n
              This example shows how to write FetchXML query to get distinct records (exclude duplicates).<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT DISTINCT accountid,name FROM account<\/pre>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links:
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' distinct='true' version='1.0'>\r\n\t<entity name='account'> \r\n\t\t<attribute name='accountid' \/>\r\n\t\t<attribute name='name' \/>\r\n\t<\/entity>\r\n<\/fetch><\/pre>\n
              SELECT TOP using\u00a0SSIS Dynamics CRM \/ Dataverse read<\/h3>\n
              This example shows how to write FetchXML query to select TOP N rows. This setting may not work if you using preview mode.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT TOP 10 * FROM account<\/pre>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' count='10' version='1.0'>\r\n  <entity name='account'>\r\n    <all-attributes \/>\r\n  <\/entity>\r\n<\/fetch><\/pre>\n
              GROUP BY \/ Aggregate functions<\/h3>\n
              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.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT AVG(estimatedvalue) AS estimatedvalue_avg, COUNT(name) AS opportunity_count, ownerid AS ownerid\r\nFROM opportunity \r\nGROUP BY ownerid<\/pre>\n
              You can use COUNT, COUNT(attribute_name), SUM, MIN, MAX, AVG as an aggregate function<\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
                     \r\n<fetch distinct='false' mapping='logical' aggregate='true'> \r\n    <entity name='opportunity'> \r\n       <attribute name='estimatedvalue' aggregate='avg' alias='estimatedvalue_avg'  \/> \r\n       <attribute name='name' aggregate='countcolumn' alias='opportunity_count' \/> \r\n       <attribute name='ownerid' alias='ownerid' groupby='true' \/> \r\n    <\/entity> \r\n<\/fetch><\/pre>\n
              INNER JOIN (matching child records)<\/h3>\n
              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.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT a.name, c.firstname, c.lastname, c.email\r\nFROM account a\r\nINNER JOIN contact c ON a.accountid=c.parentcustomerid\r\n                     AND (c.email LIKE '%@yahoo.com'\r\n                          OR c.email LIKE '%@gmail.com'\r\n                          OR c.email LIKE '%@hotmail.com')\r\nWHERE a.revenue > 10000<\/pre>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' version='1.0'>\r\n  <entity name='account'>\r\n    <attribute name='name' \/>\r\n    <filter>\r\n      <condition attribute='revenue' operator='gt' value='10000' \/>\r\n    <\/filter>\r\n    <link-entity name='contact' from='parentcustomerid' to='accountid' alias='c' link-type='inner'>\r\n      <attribute name='firstname' \/>\r\n      <attribute name='lastname' \/>\r\n      <attribute name='email' \/>\r\n      <filter type='or'>\r\n        <condition attribute='email' operator='like' value='%@yahoo.com' \/>\r\n        <filter type='or'>\r\n          <condition attribute='email' operator='like' value='%@gmail.com' \/>\r\n          <condition attribute='email' operator='like' value='%@hotmail.com' \/>\r\n        <\/filter>\r\n      <\/filter>\r\n    <\/link-entity>\r\n  <\/entity>\r\n<\/fetch>\r\n\r\n<\/pre>\n
              LEFT OUTER JOIN (‘not in’ type query)<\/h3>\n
              This example shows how to write FetchXML query to find all leads that have no tasks, using an alias.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT lead.FullName\r\nFROM Leads as lead\r\nLEFT OUTER JOIN Tasks as ab\r\n    ON (lead.leadId  =  ab.RegardingObjectId)\r\nWHERE ab.RegardingObjectId is null<\/pre>\n
              For more information on left outer join syntax check below link
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/dn531006.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>\r\n  <entity name='lead'>\r\n    <attribute name='fullname' \/>\r\n    <link-entity name='task' from='regardingobjectid' to='leadid' alias='ab' link-type='outer'>\r\n       <attribute name='regardingobjectid' \/>\r\n    <\/link-entity>\r\n    <filter type='and'>\r\n        <condition entityname='ab' attribute='regardingobjectid' operator='null' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<fetch\/>\r\n\r\n<\/pre>\n
              ORDER BY<\/h3>\n
              This example shows how to write FetchXML query with order by specified for one or more attributes.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT firstname, lastname, email FROM contact\r\nORDER BY lastname ASC, firstname DESC<\/pre>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' version='1.0'>\r\n  <entity name='contact'>\r\n    <attribute name='firstname' \/>\r\n    <attribute name='lastname' \/>\r\n    <attribute name='email' \/>\r\n    <order attribute='lastname' \/>\r\n    <order attribute='firstname' descending='true' \/>\r\n  <\/entity>\r\n<\/fetch>    \r\n<\/pre>\n
              OPERATOR – Equal ( = )<\/h3>\n
              This example shows how to write FetchXML query using equal operator.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT name FROM account WHERE revenue=0<\/pre>\n
              For a list of supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
               <fetch mapping='logical' version='1.0'>\r\n  <entity name='account'>\r\n    <attribute name='name' \/>\r\n    <filter>\r\n      <condition attribute='revenue' operator='eq' value='0' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch   \r\n  \r\n<\/pre>\n
              OPERATOR – Not Equal ( != )<\/h3>\n
              This example shows how to write FetchXML query using not equal operator.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT name FROM account WHERE revenue != 0<\/pre>\n
              For a list of supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' version='1.0'>\r\n  <entity name='account'>\r\n    <attribute name='name' \/>\r\n    <filter>\r\n      <condition attribute='revenue' operator='ne' value='0' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch><\/pre>\n
              OPERATOR – Greater Than ( > )<\/h3>\n
              This example shows how to write FetchXML query using greater than the operator.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT name FROM account WHERE revenue > 0<\/pre>\n
              For a list of supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' version='1.0'>\r\n  <entity name='account'>\r\n    <attribute name='name' \/>\r\n    <filter>\r\n      <condition attribute='revenue' operator='gt' value='0' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch><\/pre>\n
              OPERATOR – Less Than ( < )<\/h3>\n
              This example shows how to write FetchXML query using greater than the operator.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT name FROM account WHERE revenue < 0<\/pre>\n
              For a list of supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
               <fetch mapping='logical' version='1.0'>\r\n  <entity name='account'>\r\n    <attribute name='name' \/>\r\n    <filter>\r\n      <condition attribute='revenue' operator='lt' value='0' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch> \r\n<\/pre>\n
              OPERATOR – IN (Check for multiple matching values)<\/h3>\n
              This example shows how to write FetchXML query to find multiple matching values using IN operator.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT name FROM account WHERE name IN ('Microsoft', 'Yahoo', 'Google')<\/pre>\n
              For a list of supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' version='1.0'>\r\n  <entity name='account'>\r\n    <attribute name='name' \/>\r\n    <filter>\r\n      <condition attribute='name' operator='in'>\r\n        <value>Microsoft<\/value>\r\n        <value>Yahoo<\/value>\r\n        <value>Google<\/value>\r\n      <\/condition>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch>  \r\n<\/pre>\n
              OPERATOR – NULL (Check for NULL values)<\/h3>\n
              This example shows how to write FetchXML query to find NULL values (search for records where email not specified).<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT firstname, lastname, email FROM contact WHERE email is NULL<\/pre>\n
              For a list of supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
               <fetch mapping='logical' version='1.0'>\r\n  <entity name='contact'>\r\n    <attribute name='firstname' \/>\r\n    <attribute name='lastname' \/>\r\n    <attribute name='email' \/>\t\r\n    <filter>\r\n      <condition attribute='email' operator='null' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch>\r\n<\/pre>\n
              OPERATOR – NOT NULL (Check for NOT NULL values)<\/h3>\n
              This example shows how to write FetchXML query to find NOT NULL values (search for records where email not specified).<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT firstname, lastname, email FROM contact WHERE email is NOT NULL<\/pre>\n
              For a list of supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' version='1.0'>\r\n  <entity name='contact'>\r\n    <attribute name='firstname' \/>\r\n    <attribute name='lastname' \/>\r\n    <attribute name='email' \/>\t\r\n    <filter>\r\n      <condition attribute='email' operator='not-null' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch>  \r\n<\/pre>\n
              OPERATOR – LIKE (Pattern search)<\/h3>\n
              This example shows how to write FetchXML query using LIKE operator to match string by pattern search.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT name FROM account WHERE name LIKE 'zappy%'<\/pre>\n
              For a list of supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch mapping='logical' version='1.0'>\r\n  <entity name='account'>\r\n    <attribute name='name' \/>\r\n    <filter>\r\n      <condition attribute='name' operator='like' value='zappy%' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch><\/pre>\n
              OPERATOR – NOT LIKE (Pattern search)<\/h3>\n
              This example shows how to write FetchXML query using LIKE operator to match string by pattern search.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT name FROM account WHERE name NOT LIKE 'zappy%'<\/pre>\n
              For a list of supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
                <fetch mapping='logical' version='1.0'>\r\n  <entity name='account'>\r\n    <attribute name='name' \/>\r\n    <filter>\r\n      <condition attribute='name' operator='not-like' value='zappy%' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch>\r\n<\/pre>\n
              OPERATOR – UNDER (Find all children for given hierarchy)\u00a0with the\u00a0SSIS Dynamics CRM \/ Dataverse read task<\/h3>\n
              This example shows how to write FetchXML query to get children count for a given hierarchy.<\/p>\n
              For more information about querying hierarchical data check below link
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/dn817893.aspx<\/a><\/p>\n
              For a list of supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch distinct='false' no-lock='false' mapping='logical'>\r\n  <entity name='account'>\r\n    <attribute name='name' \/>\r\n    <attribute name='accountid' \/>\r\n    <attribute name='accountid' rowaggregate='CountChildren' alias='AccountChildren'\/>\r\n    <filter type='and'>\r\n      <condition attribute='accountid' operator='under' value='{0}' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch>\r\n<\/pre>\n
              Using DateTime Operators\u00a0with the\u00a0SSIS Dynamics CRM \/ Dataverse read task<\/h3>\n
              This example shows how to write FetchXML query to get all tickets which are created 30 mins before.<\/p>\n
              Other similar operators are
              \nolderthan-x-hours
              \nolderthan-x-days
              \nolderthan-x-weeks
              \nolderthan-x-months
              \nolderthan-x-years<\/p>\n
              For a list of supported DateTime operators
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg334216.aspx<\/a><\/p>\n
              For a list of all supported operators check below XSD and search for name=”operator”
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
               <fetch>\r\n  <entity name='incident'>\r\n    <attribute name='title' \/>\r\n    <attribute name='ticketnumber' \/>\r\n    <attribute name='createdon' \/>\r\n    <attribute name='incidentid' \/>\r\n    <filter type='and'>\r\n      <condition attribute='createdon' operator='olderthan-x-minutes' value='30' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch>\r\n<\/pre>\n
              Query – All activities (sort, outer join, alias) with the\u00a0SSIS Dynamics CRM \/ Dataverse read task<\/h3>\n
              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.<\/p>\n
              This FetchXML is the equivalent of below SQL<\/p>\n
              SELECT a.*\r\n, l.firstname as lead_name,l.emailadddress1 as lead_email1\r\n, c.firstname as contact_name\r\nFROM activitypointer a\r\nLEFT OUTER JONI lead l ON a.regardingobjectid=l.leadid\r\nLEFT OUTER JONI lead c ON a.regardingobjectid=c.contactid\r\nORDER BY a.createdon DESC<\/pre>\n
              To build custom FetxhXML
              \n1. Login to your Dynamics CRM \/ Dataverse Portal
              \n2. Click on Advanced Find icon (Found next to the search textbox)
              \n3. Once done building query, Click on Download FetchXML icon. Copy that XML into Query editor of Dynamics CRM \/ Dataverse Source.<\/p>\n
              For more information about FetchXML query syntax check below links :
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328117.aspx<\/a>
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg328332.aspx<\/a><\/p>\n
              For complete syntax and validation rule for FetchXML check below XSD
              \n              https:\/\/msdn.microsoft.com\/en-us\/library\/gg309405.aspx<\/a><\/p>\n
              <fetch version='1.0' mapping='logical'>\r\n\t<entity name='activitypointer'>\r\n\t\t<attribute name='subject' \/>\r\n\t\t<attribute name='ownerid' \/>\r\n\t\t<attribute name='regardingobjectid' \/>\r\n\t\t<attribute name='activitytypecode' \/>\r\n\t\t<attribute name='scheduledstart' \/>\r\n\t\t<attribute name='scheduledend' \/>\r\n\t\t<attribute name='instancetypecode' \/>\r\n\t\t<attribute name='community' \/>\r\n\t\t<attribute name='actualend' \/>\r\n\t\t<attribute name='createdon' \/>\r\n\t\t\r\n\t\t<link-entity name='lead' from='leadid' to='regardingobjectid' link-type='outer' alias='l'>\r\n\t\t\t<attribute name='firstname' alias='lead_name' \/>\r\n\t\t\t<attribute name='emailaddress1' alias='lead_email1' \/>\r\n\t\t\t<attribute name='companyname' alias='lead_company' \/>\r\n\t\t<\/link-entity>  \r\n\t\t\r\n\t\t<link-entity name='contact' from='contactid' to='regardingobjectid' link-type='outer' alias='c'>\r\n\t\t\t<attribute name='firstname' alias='contact_name' \/>\r\n\t\t<\/link-entity>  \r\n\r\n\t\t<order attribute='createdon' descending='true' \/>\r\n\t<\/entity>\r\n<\/fetch><\/pre>\n
              How to use SSIS variables with Fetch XML in SSIS for SSIS Dynamics<\/h2>\n
              CRM read<\/h2>\n
              You can include variables in an XML fetch.<\/p>\n
                \n
              1. \n        First, create your variable in the SSDT. Go to SSIS and Variables and create your variable:<\/p>\n
                        
                \"ssis-variable-value\"<\/a>
                SSIS variable value<\/p><\/div>\n    <\/li>\n<\/ol>\n
                In addition, create your fetch, include the variable in the XM ({{User::value}}):<\/p>\n
                <fetch mapping='logical' version='1.0'>\r\n  <entity name='account'>\r\n    <attribute name='name' \/>\r\n    <filter>\r\n      <condition attribute='revenue' operator='eq' value='{{User::value}}' \/>\r\n    <\/filter>\r\n  <\/entity>\r\n<\/fetch>\r\n<\/pre>\n
                Conclusion about the\u00a0SSIS Dynamics CRM \/ Dataverse read task<\/h2>\n
                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<\/a> to explore Dynamics CRM \/ Dataverse integration possibilities in SSIS.<\/p>\n
                References about\u00a0SSIS Dynamics CRM \/ Dataverse read<\/h2>\n