How to set all Party list values while migrating data of Activities using SSIS

While doing data migration of Activity entities like Email, Phone call which has Party list fields like to, from, bcc, cc etc. where we set all values i.e. lookups of different entities like Account, Contact, Lead, User, Queue etc. and manually entered an email address for e.g. abc@gmail.com. While setting these party-list values, I was unable to set manually entered email address value because it is not an entity, it is a simple field which holds values in backend.

The main cause behind this is while setting other lookups like account, contact etc. we have logical name of entities and its object type code i.e. account has 1, contact has 2 etc., But in case of manually entered email address we do not have its logical name because it is a Field, so due to this while setting the party list data I was unable to set such values in it.

This blog will help you to fix this issue and how to set all party list values including address used value while migrating Activities data.

Solution:

To set all values of party-list field we used Script component where we to write a code that will update the records. To achieve this we followed below steps:

Step 1:

Initially, we need to create project level connection parameters as shown in below screenshot:

Step 2:

Once connection parameters created then we need to create SSIS package for activity for e.g. Email where we will create email record in Destination CRM as highlighted in below screen clip:

While creating record in Destination CRM, initially we need to remove mapping of party list fields as shown in below screen clip:

Step 3:

Once record created in Destination CRM after then we need to update party list values of record in Destination CRM, for that follow below steps:

• To updating the record we need a Script component of Kingsway soft tool where we have to write the code to update party list of records. As we can see in below screen clip, the SSIS package initially creates record after then updates the record by using update() with the help of Microsoft.Xrm.sdk and Microsoft.Crm.sdk.proxy DLL’s.

• In above-highlighted portion of screen clip, initially we need to set parameters which we created in Step 1 as shown in below screen clip :

• Once we have done with the above step, we need to create one folder where we have to place Xrm.sdk and Microsoft.Crm.sdk.proxy DLL files which we are going to add them in references as shown in below screen clip:

                             References                                                        Namespace’s

• After adding references and namespaces we need to add new class file as shown in below screen clip which contains code which is use to update the record:
  

  Write below code in CRMHelper.cs file

Write below code in main.cs file

• Below is the code of GetActivity() function

GetActivity() function required to pass 3 parameters i.e. Input Row, Activity name and Participation type mask as we can see in above screen clip.

private EntityCollection GetActivity(ref Input0Buffer Row, string Activity, string ParticipationTypeMask)
    {
        #region FunctionLevelVariable
        string functionName = "GetActivity";
        int RegObjTypecode;
        EntityCollection ary = new EntityCollection();
        #endregion
        try
        {
            //Connectionstring to get connect to DB
            string connectionString = "Data Source=sql(i.e. Server Name);Initial Catalog=CRM(i.e. Database Name);Integrated Security=true";
            string sqlQuery = string.Empty;
            SqlConnection sqlConnection = null;
            int partylistCount = 0;
            string temp = string.Empty;
            DataSet ActivityDataSet = new DataSet();
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            sqlConnection = new SqlConnection(connectionString);
sqlQuery = "select FilteredActivityParty.PartyId, FilteredActivityParty.PartyObjectTypeCode, FilteredActivityParty.ParticipationTypeMask, FilteredActivityParty.PartyIdName, FilteredActivityParty.ActivityId, FilteredActivityParty.IsPartyDeleted, FilteredActivityParty.AddressUsed, FilteredActivityParty.PartyIdName from FilteredActivityParty ";
           	 sqlQuery += " inner join” + Activity + " on " + Activity + ".ActivityId = FilteredActivityParty.ActivityId";
            	sqlQuery += " where " + Activity + ".ActivityId ='" + Row.activityid.ToString() + "'"; 
            SqlCommand command = sqlConnection.CreateCommand();
            command.CommandText = sqlQuery;
            command.CommandType = CommandType.Text;
            dataAdapter.SelectCommand = command;
   dataAdapter.Fill(ActivityDataSet);
//Retrive parties based on its ParticipationTypeMask
DataRow[] parties = ActivityDataSet.Tables[0].Select("ParticipationTypeMask = " + ParticipationTypeMask);

            //check length 
            if (parties.Length > 0)
            {
                foreach (DataRow row in parties)
                {
                    #region foreach level variables
                    Entity apto = new Entity("activityparty");
                    Entity ap1 = new Entity("activityparty");
                    Entity ap2 = new Entity("activityparty");
                    Entity ap4 = new Entity("activityparty");
                    Entity ap8 = new Entity("activityparty");
                    Entity ap2020 = new Entity("activityparty");
                    #endregion

                    if (partylistCount == 120)
                    {
                        break;
                    }
                    int PartyObjectTypeCode = Convert.ToInt32(row["PartyObjectTypeCode"]);
                    switch (PartyObjectTypeCode)
                    {
                        case 0:

                            if (row["IsPartyDeleted"].Equals(false))
                            {
                                if (PartyObjectTypeCode == 0)
                                {
                                    if (row["AddressUsed"] != "" || row["AddressUsed"] !=string.Empty)
                                    {
                                        apto["addressused"] = row["AddressUsed"];
                                        ary.Entities.Add(apto);
                                    }
                                }
                            }
                            break;

                        case 1:

                            if (row["IsPartyDeleted"].Equals(false))
                            {
                                ap1["partyid"] = new EntityReference("account", new Guid(row["PartyId"].ToString()));
                                ary.Entities.Add(ap1);
                            }
                            break;

                        case 2:

                            if (row["IsPartyDeleted"].Equals(false))
                            {
                                ap2["partyid"] = new EntityReference("contact", new Guid(row["PartyId"].ToString()));
                                ary.Entities.Add(ap2);
                            }
                            break;
case 4:

                            if (row["IsPartyDeleted"].Equals(false))
                            {
                                ap4["partyid"] = new EntityReference("lead", new Guid(row["PartyId"].ToString()));
                                ary.Entities.Add(ap4);

                            }
                            break;

                        case 8:

                            if (row["IsPartyDeleted"].Equals(false))
                            {
                                string userGUID = getUserGUID(row["PartyIdName"].ToString());
                                ap8["partyid"] = new EntityReference("systemuser", new Guid(userGUID));
                                ary.Entities.Add(ap8);
                            }
                            break;

                        case 2020:
                            if (row["IsPartyDeleted"].Equals(false))
                            {
                                ap2020["partyid"] = new EntityReference("queue", new Guid(row["PartyId"].ToString()));
                                ary.Entities.Add(ap2020);
                            }
                            break;
                    }
                }
            }
            return ary;
        }
        catch (Exception e)
        {
            return null;
        }
}

In above code, we can see the highlighted function in Yellow i.e. getUserGUID() function which is used to get users GUID of destination CRM based on the name of Users as they are present in Source CRM. To get the Destination Users GUID please use below code:

public string getUserGUID(string onpremiseUsername)
    {
        #region Function Level Varibles
        string functionName = "getUserGUID";
        var listOfOnPreUserNames = new List<string>();
        var listOfOnlineUserNameIds = new List<KeyValuePair<string, string>>();
        string OnLineUseGUID;
        int indexOfGuid;
        #endregion
        try
        {
            #region List of Onpremise users
            listOfOnPreUserNames.Add("ABC");
            listOfOnPreUserNames.Add("XYZ");

#region List of Online 365 User name & ID
            listOfOnlineUserNameIds.Add(new KeyValuePair<string, string>("da4a442d-32b5-e811-a987-000d3a18032d", " ABC "));
            listOfOnlineUserNameIds.Add(new KeyValuePair<string, string>("40976433-32b5-e811-a987-000d3a18032d", " XYZ "));
#endregion
//get index of founded user
            indexOfGuid = listOfOnPreUserNames.IndexOf(onpremiseUsername);

            //if index is -1 it means user does not contain in users list
            if (indexOfGuid != -1)
            {
                //Retrive Online users GUID
                OnLineUseGUID = listOfOnlineUserNameIds[indexOfGuid].Key;
            }
            else
            {
                //set Default user
                OnLineUseGUID = "03eb25a2-122d-405d-bfaf-de5fdea4a8af";
            }
        }
        catch (Exception ex)
        {
            throw new Exception(functionName + "<<" + ex.Message);
        }
        return OnLineUseGUID;
    }

Conclusion:

Using Service.Update() in Script component we can set all party list values like Account, Contact, Users or Address used in Activities which has party list fields.