How to get related Entity Type name in Dataverse / CRM Source (Get LogicalName of Lookup) - Example of Appointment object regardingobjectid, regardingobjecttype, regardingobjectname

Problem

In this post we will see how to output related entity's name (default field) and type (i.e. Account, Contact). in SSIS Dynamics CRM / Dataverse Source. For demo purpose we will use Appointment Table but technique can be used for any other table with Lookup type

For example if you like to query regardingobjectidtype along with a few more fields like id, name then it query might look like below. But this will not work becuaseregardingobjectidtype is not a valid field.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
  <entity name="appointment" >
   <attribute name="subject" />
    <attribute name="scheduledstart" />
    <attribute name="scheduledend" />
    <attribute name="regardingobjectid" />
    <attribute name="regardingobjectidname" />
    <attribute name="regardingobjectidtype" />
  </entity>
</fetch>

Solution

To fix this issue perform the following steps

1. First step depends on which version are you using.
   How to check my SSIS PowerPack version 
   For V5.1.1 or newer
   Use Table mode or FetchXML mode. If you use FetchXML then do not include  regardingobjectidtype column (other columns fine)
   
   Like below
   
   <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
     <entity name="appointment" >
       <attribute name="subject" />
       <attribute name="scheduledstart" />
       <attribute name="scheduledend" />
       <attribute name="regardingobjectid" />
       <attribute name="regardingobjectidname" />
     </entity>
   </fetch>
   
   
   
   For V5.1.0 or older
   
   Use Table mode rather than FetchXML mode like below. 
   
   
2. Now Go to Columns Tab
3. Add <your-lookup-filed>type column manually in Columns Tab and Check Lock option
   
   Example: add regardingobjecttype
   
   
4. Run data flow
   
   

NOTE: Preview wont show regardingobjecttype so you must run data flow to see value of that column manually added

Contact Us

if you have more questions feel free to contact us via Live chat or email at [e-mail]

https://zappysys.com/support/