In your CRM SSIS/ETL project, you may sometimes run into a situation that you need to perform a cascaded Lookup, it can be a challenge if you don't have the right tool or if you are not using the tool correctly.
Let's first discuss a bit of the usage scenario. Let's say that you have a CRM custom entity called Project (or myorg_project as its physical name in CRM), the entity may use an internal resource or an external resource, so you have two custom lookup fields which are:
- myorg_internalresourceid, this is a lookup field that points to the CRM systemuser entity.
- myorg_externalresourceid, this is the other lookup field that points to the CRM contact entity.
Your business requirement dictates that only one of the above two lookup fields should have a value. When writing to this particular entity, it is very likely that you may have the resource name, but you don't know the resource type, and it can be a system user or a contact. With this particular situation in mind, you would design your ETL process to perform a cascaded lookup on the two lookup fields, say you want to perform a lookup of the systemuser entity first, if it finds a match, the ETL process should write to the myorg_internalresourceid field. If it doesn't find a match, then it would attempt a lookup on contact entity, and write to the myorg_externalresourceid field.
This particular requirement can be achieved using the SSIS Integration Toolkit for Microsoft Dynamics 365/CRM by taking advantage of the Text Lookup feature which comes with the capability to skip rows when the lookup fails. Let's see how the data flow should look like to achieve this business requirement.
As you can see, you would be using two destination components, with the first one to have the myorg_internalresourceid field mapped with Text Lookup configured to perform the lookup using the Primary Key field (which is fullname) of the systemuser entity, notice that we have chosen the option "Skip rows if lookup fails" option which is on the left-bottom corner of the Text Lookup configuration window.
When we have the "Skip rows if lookup fails" option enabled, the destination component will come with a "Skipped Rows" output, which can be connected to another CRM destination component right after. The following is the screen that you will get prompted when you tried to connect the secondary destination component to the first one (which comes with two outputs - Default Output and Skipped Rows output).
The second CRM destination component should perform the same action, but with the secondary lookup field mapped this time, which is something like the following.
A few notes before we conclude the blog post.
- In the "Skipped Rows" output, there is an additional field available called "SkipReasonMessage" which contains the message that tells why the record has been skipped. From best practice perspective, you should always check this column before you send to another destination component for cascaded lookup. You would do that using a SSIS Conditional Split component. I have skipped this because I know for sure the skipped rows can only be initiated from skipped lookup in my setup. There are other scenarios that can cause an input row to go to the "Skipped Rows" output - in particular if you are using an action along with the "Ignore" option selected as the "Handling of Multiple Matches".
- You can technically chain another destination component after the second destination component, so the lookup keeps going on as needed.
- You cannot use the "Alternate Key" lookup method in this particular setting as the "Skip Record if Lookup Fails" option does not apply when the Alternate Key lookup method is used.
This is a fairly common integration scenario, I hope this blog post helps.