SSIS Dynamics CRM – Read / Import data into SQL Server

Contents

Introduction

Today Microsoft Dynamics CRM is one of the most popular CRMs in the world. SSIS Dynamics CRM read operation can be achieved using Dynamics CRM Source Connector. It allows connecting to Dynamics CRM 365 Online or On-Premises Installation / Hosted CRM instance (IFD – internet facing deployment). You can read data from Dynamics CRM 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 365 (Online) data using Dynamics CRM 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 read component

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

SSIS Dynamics CRM Source – Video Tutorial

Getting started with SSIS Dynamics CRM read component

Configure Dynamics CRM Connection

  1. In the connection manager, press the new button to create a new connection:
    SSIS create a new connection CRM Dynamics

    SSIS create a new connection CRM Dynamics

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

    SSIS Dynamics CRM credentials

How to create a Dynamics CRM Source

  1. In the control flow, drag and drop the data flow:
  2. Drag and drop the ZS Dynamics CRM Source:
    ssis dynamics crm source

    SSIS Dynamics CRM source

  3. Second, select the connection created before and select the account table:
    ssis entity table account

    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.

  1. First, drag and drop the OLEDB Destination and join with our ZS CRM Dynamics source:
    ssis source dynamics sql destination

    SSIS source dynamics SQL destination

  2. Make sure to select the account table from Dynamics:
    ssis entity table account

    SSIS Dynamics CRM read entity table account

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

    SSIS OLEBD SQL Server

  4. Go to the mapping page to map all the source and destination columns:
    dynamics import data SSIS Dynamics CRM read

    SSIS Dynamics CRM read mapping

Load Dynamics CRM Data to SQL Server

  1. Execute the package:
    ssis start package

    SSIS start package

  2. Finally, you can see the data exported in SQL Server. Select the data from the CRM Dynamics table:
Data exported from crm to sql server

Data exported from SSIS Dynamic CRM read

How to query Dynamics CRM

You can query dynamics CRM 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.

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

 

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

 

How to generate a FetchXML query using UI

  1. The Dynamics CRM 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 fetch xml

    Dynamics CRM fetch XML

  2. For example, we will query the Accounts entity where the account name is A. Datum:
    dynamics 365 crm Create a query

    dynamics 365 CRM Create a query

  3. Next, we can download the Fetch XML:
    dynamics crm xml download

    dynamics crm 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

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

SELECT Specified Columns and Alias with SSIS Dynamics CRM

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

SELECT TOP using SSIS Dynamics CRM 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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

OPERATOR – Equal ( = )

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

This FetchXML is the equivalent of below SQL

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

OPERATOR – Not Equal ( != )

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

This FetchXML is the equivalent of below SQL

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

OPERATOR – Greater Than ( > )

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

This FetchXML is the equivalent of below SQL

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

OPERATOR – Less Than ( < )

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

This FetchXML is the equivalent of below SQL

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

OPERATOR – UNDER (Find all children for given hierarchy) with the SSIS Dynamics CRM 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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

Using DateTime Operators with the SSIS Dynamics CRM 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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

Query – All activities (sort, outer join, alias) with the SSIS Dynamics CRM 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

To build custom FetxhXML
1. Login to your Dynamics CRM 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 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

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

CRM read

You can include variables in an XML fetch.

  1. First, create your variable in the SSDT. Go to SSIS and Variables and create your variable:
    ssis-variable-value

    SSIS variable value

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

Conclusion about the SSIS Dynamics CRM 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 to SQL Server. Download SSIS PowerPack to explore Dynamics CRM integration possibilities in SSIS.

References about SSIS Dynamics CRM read

Posted in SSIS Dynamics CRM Source and tagged , , .