How to – Use Lookup Transformation and Cache Transformation in SSIS for Dynamics 365 (using Kingswaysoft)


Recently while working in data migration project, we had a requirement to insert a record in CRM from SQL Database and assign it to the team insert.

For e.g.

We had a Transaction Record that had a Brand lookup in it i.e. n – 1 relationship.

And the Brand record had a lookup of Team in it again n – 1. So basically, while inserting the transaction record we wanted to assign the record to the team of the corresponding brand in it.

So, for this, we had to set the owner id and owner id type field while inserting the record.

And for owner id i.e. team GUID we had to do a lookup on the Brand entity.

To achieve so, we did the following step

Added 2 Data flow task in the Control Flow, one for Caching the Brand entity’s detail and other for using the fetching data from SQL DB, Lookup Transformation task and inserting the data into CRM.

For the first data flow task,

We added the Dynamics CRM Source and fetched the Brand Name and Team Lookup’ GUID field.

name field which will be used for mapping and teamid field whose value we need from it.

Then we added a Cache Transform task with following details,

Now for our second data task flow,

First, we added the OLE DB Source to fetch data from the source SQL View, having Brand (string) column in it.

Then we added the Lookup Transformation task with following properties,

Setting Connection to Cache Connection Manager defined earlier.

Now the most important part, the columns properties. Here we defined the mapping between the Brand field of the SQL View and name field of the Brand Entity of CRM and selected the teamid field as the lookup column whose value we would like to fetch as shown below.

Then we added Derived Column task to add one more column for specifying ownerid type field to be used in CRM Destination Component while mapping. We defined the field type as string and hardcoded the value as “team”.

And as the final step mapped the above fields to the corresponding ownerid and owneridtype field in CRM Destination Component for Create operation.

For ownerid, we selected the below option as we were setting the GUID of the team.

On running the package, we can see the transaction records getting created and properly assigned to the corresponding team of the brand.

The transaction record with Team set as Owner.

Brand record with Team lookup in it.

Hope it helps..

Advertisements

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology !

4 thoughts on “How to – Use Lookup Transformation and Cache Transformation in SSIS for Dynamics 365 (using Kingswaysoft)”

Please share your thoughts

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Welcome to the Blog & Website of Paul Andrew

Technical Leadership Centred Around the Microsoft Data Platform

Deriving Dynamics 365

Deriving Solutions and features on Power Platform/Dynamics 365

The CRM Ninja

Thoughts & musings from a Dynamics 365 Ninja!

CRM for You and Me

Customer Relationship Mgt App for the Professional

D CRM Explorer

Learn about Microsoft Dynamics CRM Power Platform customization and implementation and other cool stuffs

Stroke // Jonas Rapp

I know pre-stroke. I will improve who I was.

Power Melange

Power Melange By Shalinee

Clavin's Blog

Power Automate - Power Apps - SharePoint Online - Azure - Nintex - K2 - Artificial Intelligence

Sat Sangha Salon

An Inquiry in Being

The Indoencers

The Influencers & Influences of Indian Music

Monika Halan's blog

Hand's-free money management

D365 Demystified

A closer look at Microsoft Dynamics 365.

Microsoft Mate (msftmate) - Andrew Rogers

Experienced consultant primarily focused on Microsoft Dynamics 365 and the Power Platform

Knowhere365

Specific topics by Django Lohn on the whole Microsoft365 Stack

Manmit Rahevar's Blog

One Stop Destination for Microsoft Technology Solutions

MG

Naturally Curious

Brian Illand

Power Platform and Dynamics 365

Steve Mordue MVP

A Microsoft Business Applications MVP

Subwoofer 101

Bass defines your home theater

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.

Everything D365

Discovering Azure DevOps and D365 Business Applications

Tech Wizard

Lets do IT Spells

XRM Tricks (Power Platform & Dynamics CRM )

Power Platform & Dynamics CRM

CRM TIPS BY PRM

Mail to crmtipsbyprm@gmail.com for queries and suggestions

nijos.dev

Giving back to the community what I have learned

xrm CRM Dynamics

Dynamics CRM Technical & Functional Info

Dynamics 365 Blogs - Explained in unique way

Sometimes you need to look at things from different perspective.

CRM Keeper

Dynamics 365 Customer Engagement, CRM, Microsoft CRM, Dynamics CRM

EVOLVED365

Step into the world of a Dynamics 365 Consultant

Dianamics PCF Lady

Diana & Dynamics 365 & Power Platform

Sara Lagerquist

No Code Customization Concepts

innovativeaj

developer, designer, writer, fun loving, patriotic, humble and a sweet person inside out :) love and respect people who have a great sense of humor.

Temmy Wahyu Raharjo

dreaming to be a clean coder and TDD minded programmer.

%d bloggers like this: