SSIS Dynamics CRM Source

Help > Source Adapters > Dynamics CRM Source >

SSIS Dynamics CRM Source
SSIS Dynamics CRM Source can be used to read data from Microsoft Dynamics CRM Service such as Dynamics 365 Online or On-Premises installation. You can use simple Table mode or write custom Query to extract desired data.

Video Tutorial

Step-By-Step

In this tutorial you will learn how to read data from DynamicsCRM using with DynamicsCRM Source(In this case its Table and Query Mode).

Firstly, You need to Download and Install SSIS ZappySys PowerPack.
Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
Double click on the Data Flow task to see Data Flow designer surface.
Here, In Visual Studio, drag and drop the ZS DynamicsCRM Source in the design surface.
We need DynamicsCRM Connection.

How to create DynamicsCRM Connection.

Right click in the Connection Manager panel and click on New Connection.
Select ZS-DYNAMICS-CRM Connection Manager from the Connection Managers list and Click on Add Button.
In the connection manager UI, set Authentication type to CRM Online (Office 365), enter user name and password. Select organization.
Click on Test Connection to check configure correct or not.
Click on OK button to save DynamicsCRM Connection Manager configure setting UI.

How to read data from DynamicsCRM table using Dynamics Source(Table Mode).

Double click on DynamicsCRM source to configure it.
Select connection we have created, set AccessMode to Entity and select table you want to read data.
Click on Preview button to see Data Preview.
Click on OK button to save configure setting UI.
From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
Now single click on the DynamicsCRM Source, once you see blue arrow from source ... connect it to Trash Destination.
Double click on ZS Trash Destination to Configure it.
Click on OK button to save configure setting UI.
Thats all, just run or execute your package and see data.

How to read data from DynamicsCRM table using Dynamics Source(Query Mode).

Here, we are getting data using Query Mode with Dynamic expression value.
Lets, create a Variable with correct DataType and Value, use following image.
Double click on DynamicsCRM source to configure it.

Select connection we have created before, set AccessMode to Fetch XML. Click on View Examples and select option or you can use following FetchXML Query. Click here for more FetchXML Query Examples.

<fetch mapping='logical' version='1.0'>
	<entity name='account'>
	<attribute name='{{User::ColumnName}}'/> 
	<attribute name='revenue'/> 
		<filter type='and'>
			<condition attribute='{{User::ColumnName}}' operator='like' value='{{User::value}}' />
			<condition attribute='revenue' operator='gt' value='0' />
		</filter>
	</entity>
</fetch>

Now, click on Insert Variable and select of them.
SSIS Dynamics CRM Source - Read data in Query mode using FetchXML

Click on Preview button to see Data Preview.
Click on OK button to save configure setting UI.
From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
Now single click on the DynamicsCRM Source, once you see blue arrow from source ... connect it to Trash Destination.
Double click on ZS Trash Destination to Configure it.
Click on OK button to save configure setting UI.
Thats all, just run or execute your package and see data.
Check this article for more instructions with examples (Read from Dynamics CRM).

Properties

Property Name

Description

AccessMode

AccessMode for Dynamics CRM Data

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option	Description
Table [0]	Entity
Query [1]	Fetch XML

LoggingMode

LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option	Description
Normal [0]	Normal
Medium [1]	Medium
Detailed [2]	Detailed
Debugging [3]	Debugging

PrefixTimestamp

When you enable this property it will prefix timestamp before Log messages.

TableName

Source object name from where you want to read data. This option only used when AccessMode=Table

BatchSize

How many rows you want to fetch in each request.

Query

Crm query to read data. This option only used when AccessMode=Query

MaxRows

Maximum number of rows to fetch from source. 0=Unlimited

IncludeDeleted

Set this to true if you want to read deleted records

InputDateTimeConversionMode

Specifies how to convert input date/time string / variable values (For query mode only)

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option	Description
None [0]	Default
UtcToLocal [1]	Output As Local Time
LocalToUtc [2]	Output As UTC Time
FormatISO [3]	Output As ISO format

OutputDateTimeConversionMode

Specifies how to convert output date/time coming from source (e.g. If CRM is sending date in UTC you may automatically convert it to Local DateTime using this option)

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option	Description
None [0]	Default
UtcToLocal [1]	Output As Local Time
LocalToUtc [2]	Output As UTC Time
FormatISO [3]	Output As ISO format

FetchXML Query Examples

SELECT All Columns (*)

This example shows how to write simple FetchXML query to select all columns.

This FetchXML is the equivalent of below SQL

SELECT * FROM account

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

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

SELECT Specified Columns and Alias

This example shows how to write simple FetchXML query to select two columns and use 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

<fetch mapping='logical'> 
	<entity name='account'>
		<attribute name='accountid'/> 
		<attribute name='name' alias = 'accountname'/> 
	</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%'

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

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

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

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%'
    )

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

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

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

SELECT TOP

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

<fetch mapping='logical' count='10' version='1.0'>
  <entity name='account'>
    <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 Aggregate function in SQL Query you can also use alias for 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

You can use COUNT, COUNT(attribute_name), SUM, MIN, MAX, AVG as 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

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

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

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

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

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

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

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

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

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

For 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

<fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='revenue' operator='eq' value='0' />
    </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

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

OPERATOR - Greater Than ( > )

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

This FetchXML is the equivalent of below SQL

SELECT name FROM account WHERE revenue > 0

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

OPERATOR - Less Than ( < )

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

This FetchXML is the equivalent of below SQL

SELECT name FROM account WHERE revenue > 0

<fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='revenue' operator='gt' value='0' />
    </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')

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

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

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

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

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

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

<fetch mapping='logical' version='1.0'>
  <entity name='account'>
    <attribute name='name' />
    <filter>
      <condition attribute='name' operator='like' value='zappy%' />
    </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%'

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

OPERATOR - UNDER (Find all children for given hierarchy)

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

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

Using DateTime Operators

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 list of supported datetime operators
https://msdn.microsoft.com/en-us/library/gg334216.aspx

For 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

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

Query - All activities (sort, outer join, alias)

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

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

Setting UI

Articles / Tutorials

Click here to see all articles for [SSIS Dynamics CRM Source] category

SSIS PowerPack v2.9.5 released (Maintenance Release)

What’s New In this release we focused on fixing some bugs with Dynamics CRM Source, Dynamics CRM Destination and Reporting Services Task. Version 2.9.6.10621 [Jun 24, 2019] New Features/Improvements NEW: Excel Source – Add option to read from any sheet by number rather than hard coded table name (e.g. $4$ , $5$ … ) Bug fixes […]

SSIS Dynamics CRM – Read / Import data into SQL Server

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 […]

Content

Video Tutorial

Step-By-Step

How to create DynamicsCRM Connection.

How to read data from DynamicsCRM table using Dynamics Source(Table Mode).

How to read data from DynamicsCRM table using Dynamics Source(Query Mode).

Properties

FetchXML Query Examples

SELECT All Columns (*)

SELECT Specified Columns and Alias

WHERE (AND conditions)

WHERE (OR conditions)

WHERE (Mixing AND / OR conditions)

SELECT DISTINCT (Exclude duplicates)

SELECT TOP

GROUP BY / Aggregate functions

INNER JOIN (matching child records)

LEFT OUTER JOIN ('not in' type query)

ORDER BY

OPERATOR - Equal ( = )

OPERATOR - Not Equal ( != )

OPERATOR - Greater Than ( > )

OPERATOR - Less Than ( < )

OPERATOR - IN (Check for multiple matching values)

OPERATOR - NULL (Check for NULL values)

OPERATOR - NOT NULL (Check for NOT NULL values)

OPERATOR - LIKE (Pattern search)

OPERATOR - NOT LIKE (Pattern search)

OPERATOR - UNDER (Find all children for given hierarchy)

Using DateTime Operators

Query - All activities (sort, outer join, alias)

Setting UI

See Also

Articles / Tutorials

SSIS PowerPack v2.9.5 released (Maintenance Release)

SSIS Dynamics CRM – Read / Import data into SQL Server