Data Exchange Framework Mapping multiple source fields to Single SItecore Field

In this blog, I will discuss about Data exchange framework and mapping the multiple source items (Sql Columns) to single target item. In a project, we needed to map the fields from two different SQL columns (latitude and longitude) into a single Sitecore field(LatLong).Sitecore supports adding multiple fields to source accessors from version 9.0.But we are using 8.2 version and do not have the ability to add the multiplource fields and map them to one sitecore field. This single LatLong field will be used for spatial Solr search at later point.Note – The code in this blog is written and tested in Sitecore version 8.2, Data Exchange Framework 1.4.1 with SQL provider. Other versions may have different API/Code.Mapping multiple fields from SQL server to single sitecore field can be achieved by writing a custom value converter and modifying sitecore converter fields.Follow the below steps to achieve the multiple field mapping to single field in sitecore version 8.2.

1. Create a new value mapping by duplicating the Value Mapping in the location /sitecore/templates/Data Exchange/Framework/Data Access/Mapping.     
2. Change the type of the source item to Multi-List and Set the data source to the location of the database tables Value accessor sets ‘/sitecore/system/Data Exchange/SQL Data Exchange Tenant/Location Data Access/Value Accessor Sets/Providers/Database/Table Fields’ 
3.  Since we have created a new mapping in Sitecore, we need to override the default mapping class from Sitecore.Dataexchange dll.For doing this, we need to write a c# class and override the IMapping class and implement the Convert method.In the following code supportedIds attribute is the new multi column value mapping we created in step 2.
   
   

   using Sitecore.DataExchange.Attributes;using Sitecore.DataExchange.DataAccess;using Sitecore.DataExchange.DataAccess.Mappings;using Sitecore.DataExchange.DataAccess.Readers;using Sitecore.DataExchange.Repositories;using Sitecore.Services.Core.Model;using System.Collections.Generic;using System.Linq;namespace Sitecore.DataExchange.Converters.DataAccess.Mappings{    [SupportedIds(new string[] { "{A4990389-D97E-4BA7-BF39-9584C299C3D6}" })]    public class CustomMappingConverter : BaseItemModelConverter<IMapping>    {        public CustomMappingConverter(IItemModelRepository repository)          : base(repository)        {        }        public override IMapping Convert(ItemModel source)        {            if (!this.CanConvert(source))                return (IMapping)null;            List<IValueAccessor> multipleList = base.ConvertReferencesToModels<IValueAccessor>(source, "SourceAccessor").ToList();            Mapping mapping = new Mapping()            {                Identifier = this.GetStringValue(source, "ItemID"),                SourceAccessor = this.ResolveValueAccessor(true, multipleList),                TargetAccessor = this.ConvertReferenceToModel<IValueAccessor>(source, "TargetAccessor"),                Enabled = this.GetBoolValue(source, "Enabled"),                IgnoreNullValues = this.GetBoolValue(source, "IgnoreNullValues"),                SourceValueTransformer = this.ConvertReferenceToModel<IValueReader>(source, "SourceValueTransformer")            };            IEnumerable<IApplyMappingRule> models = this.ConvertReferencesToModels<IApplyMappingRule>(source, "ApplyMappingRules");            if (models != null)            {                foreach (IApplyMappingRule applyMappingRule in models)                    mapping.ApplyMappingRules.Add(applyMappingRule);            }            return (IMapping)mapping;        }        protected virtual IValueAccessor ResolveValueAccessor(bool readValueToCollection, List<IValueAccessor> accessors)        {            if (!(accessors.Count > 1 | readValueToCollection))            {                return accessors.FirstOrDefault();            }            return new ValueAccessor()            {                ValueReader = new CompoundValueReader()                {                    ValueReaders =                        from r in accessors                        select r.ValueReader                }            };        }    }}

4.  In the above code, there is a custom class Code was copied from Sitecore Version 9.0 Sitecore.DataAccess.dll.Here is the code to read compound value.
   
   

   using System;using System.Collections.Generic;namespace Sitecore.DataExchange.DataAccess.Readers{    public class CompoundValueReader : IValueReader    {        public virtual ReadResult Read(object source, DataAccessContext context)        {            ReadResult readResult;            List<object> objs = new List<object>();            using (IEnumerator<IValueReader> enumerator = this.ValueReaders.GetEnumerator())            {                while (enumerator.MoveNext())                {                    ReadResult readResult1 = enumerator.Current.Read(source, context);                    if (readResult1.WasValueRead)                    {                        objs.Add(readResult1.ReadValue);                    }                    else                    {                        readResult = ReadResult.NegativeResult(DateTime.Now);                        return readResult;                    }                }                return ReadResult.PositiveResult(objs, DateTime.Now);            }            return readResult;        }        public CanReadResult CanRead(object source, DataAccessContext context)        {            if (source != null)            {                return CanReadResult.PositiveResult();            }            return CanReadResult.NegativeResult();        }    }}

5. Navigate to the standard values of Multi-column Value mapping and change the Converter type of Multi-column Value mapping to DataExchange.Converters.DataAccess.Mappings.CustomMappingConverter,Sitecore.DataExchange.Providers.SqlServer.
6. Using the new created template ‘/sitecore/templates/Data Exchange/Framework/Data Access/Mapping/Multi-Column Value Mapping’, create a new value mapping set in the location ‘/sitecore/system/Data Exchange/SQL Location Data Exchange Tenant/Value Mapping Sets/SQL Row to Facility Item/Lat Long Value Mapping’.
7. Now, create a new Value reader in the location /sitecore/templates/Data Exchange/Framework/Data Access/Value Readers and name ‘Collection To String Value Reader’.Note – This value reader is an out of the box feature in the sitecoire version 9.0.Prior versions will require this new value reader.
8. The above created template should have a field called Seperator which is single line text to separate multiple fields of the above selected source accessors.See screenshot to see.
9. Navigate to its standard values and add value, in the Separator field
11. Next step is to create a new Value reader and specify the convert type which we are going to implement in c# at later steps.Converter Type - /sitecore/system/Data Exchange/SQL Location Data Exchange Tenant/Location Data Access/Value Readers/Lat Long Value Reader.See screenshot below
12. In code project, create a new class with the LatLongValue reader and override the Read method.
    
    

    using Sitecore.DataExchange.DataAccess;using System;using System.Collections;using System.Collections.Generic;namespace Sitecore.DataExchange.Providers.SQLServer.Extensions{    public class LatLongValueReader : IValueReader    {        public virtual CanReadResult CanRead(object source, DataAccessContext context)        {            if (source != null)            {                return CanReadResult.PositiveResult();            }            return CanReadResult.NegativeResult();        }        public virtual ReadResult Read(object source, DataAccessContext context)        {            if (source == null)                return ReadResult.PositiveResult((object)null, DateTime.Now);            ICollection collection = source as ICollection;            if (collection == null)                return ReadResult.NegativeResult(DateTime.Now);            List<object> objectList = new List<object>();            foreach (object obj in collection)                objectList.Add(obj);            return ReadResult.PositiveResult((object)string.Join<object>(this.Separator, (IEnumerable<object>)objectList), DateTime.Now);        }    }}

13. Navigate to value accessor target sitecore field Sitecore Item /sitecore/system/Data Exchange/SQL Location Data Exchange Tenant/Location Data Access/Value Accessor Sets/Providers/Sitecore/Fields/LatLong and populate value reader created in step 7

 ResultThe combined value of Latitude and Longitude from SQL columns will be added to single Sitecore field as shown below.