ADF - (Part3) Integrate Data From CRM to External system Using Stored procedure

 In this blog we will see how we can use stored procedure to transform our data in ADF. 

For better understanding you can check my previous blog for better understanding.

When we integrate CRM data to external system SQL, then for  Option-Set fields present in CRM, we get Option-Set values and not Text value.

eg. we have Credit Hold option set field in CRM and after integration we are getting option set value.

To solve this problem we will create OptionSetMetaData table in SQL which will store details about option-set fields

SQL query to create OptionSetMetaData table

CREATE TABLE [dbo].[OptionSetMetadata](
	[ID] [uniqueidentifier] NULL,
	[EntityName] [varchar](50) NULL,
	[OptionSetFieldName] [varchar](50) NULL,
	[OptionSetText] [varchar](50) NULL,
	[OptionSetValue] [int] NULL
)

//To Insert Records in sql Table 
Insert Into OptionSetMetadata Values (NEWID(), 'account', 'Credit Hold', 'Yes', 1)
Insert Into OptionSetMetadata Values (NEWID(), 'account', 'Credit Hold', 'No', 1)

After creating this table, we have to create stored procedure and Apply inner join on Account and OptionSetMetaData table to get option-set text value in CreditHoldText  field of account table.

SQL query to create Stored Procedure to update CreditHoldText value for account :

Create Procedure UpdateOptionsetMetaData

as 
begin

Update Account
Set Account.CreditHoldText = OptionSetMetadata.OptionSetText
from Account
Inner Join OptionSetMetadata
On Account.CreditHold = OptionSetMetadata.OptionSetValue
Where OptionSetMetadata.EntityName = 'account'

end

Once you create this stored procedure, call this stored procedure after Copy Data activity of account.

After calling you can run pipeline and you will see optionset-text value in CreditHoldText field.

Similarly, For contact table we are getting only AccountID and not AccountName.

To solve this issue, We can create stored procedure and add logic of inner join beetween contact and account table to get AccountName. SQL query to create stored procedure

Create Procedure updateContactLookUpFields

as
begin

Update Contact
Set Contact.AccountName = Account.AccountName
from Contact
Inner Join Account
on Contact.AccountID = Account.AccountId

end

After creating stored procedure, Call this stored procedure after copy data activity of contact in similar way we have added above. Run the pipeline and we will get Account name data in AccountName field of contact

If you want more details about this, then please check out my below video :)