United Kingdom +44 (0) 1424 863 450   hello@simego.com

Team Blog

Keep up to date with everything going on at Simego.

23 November 2015

Dynamics CRM Entity Trigger

We have updated the CRM Entity trigger in Ouvvi release 2.5.350 (Data Sync v3.0.972) to support CRM 2015 and also CRM Online.


Simply enter the base URL to your Dynamics CRM Server (https://crm.dynamics.com for CRM Online) and the Organisation Name along with the Credentials. This is the same as when you connect to CRM in Data Sync as Ouvvi is actually calling the Data Sync CRM Provider for this.


The trigger runs a FetchXml query against CRM to get the MAX of modifiedon from the Entity you specify. You can optionally add a FetchXml Filter if you want to only trigger based on a subset of values in a given Entity.




Multiple Triggers for the same CRM Server will re-use the same underlying connection and therefore running multiple CRM Triggers to the same CRM Server is pretty fast.


Typically any projects linked to a CRM Trigger will be run within 30 seconds of a CRM data change with the default configuration.

21 October 2015

Automatic Schema Mapping

We have a new Schema Mapping feature in the latest Data Sync Beta release that is designed to assist in the mapping of a Source system to a Target System.


This new auto mapping feature will look at the schema column names, data types and also a snapshot of the Data to automatically generate the Schema Mapping.


Lets take an Example, Here we have the Northwind SQL Demo Database Products table as our source.




I have created a CSV file with some of the data, 10 rows and modified the names of some of the columns to see whether Data Sync an Automatically map this for us.




The default mapping gets this far, not really very good, the column names do not match enough to get any sensible default mapping.




However if we select the AutoMapper from the Schema Map Toolbar.




Select the default options.




We now get a much better result, in fact the Auto Mapper has successfully managed to map all the Columns.


Auto Mapper Results


You will notice that CategoryID and SupplierID columns are mapped correctly even though the data is very similar and we even managed to map Discontinued to Enabled based entirely on the data values.


The Options of the Auto Mapper allow you to remove columns that have no data i.e. are all NULL on the source and columns that just have a default value i.e. are all the same. This is handy when you have many columns in your data sources but you want to find the columns that contain data.

9 October 2015

Data Sync and Ouvvi Update

There have been many updates and features added to Data Sync and Ouvvi recently with the release of Data Sync 3.0.966 and Ouvvi 2.5.346.


It was time that we listed the changes so that you could see what we have been up to.


The two major features are the Ouvvi Multiple Instance support, great for DEV/PROD configuration on a single server and Data Sync Item Events allowing you programmatic control during the target write phases.




New provider that supports Amazon S3 Blobs, Read and Write Files to Amazon Blob Storage. Easily upload and download files with Amazon S3 Storage and use MD5 hash to verify the change.



Support for Azure Storage Blobs with the File System to enable simple upload and download of files into Blob Storage.


Support for Azure Storage Tables, simply synchronise your Data with Azure Table Storage. Add additional attributes and support for batch operations.


Both Azure Providers now support the connection registry and Server Explorer.




Active Directory

Active Directory provider now supports Automation Item events allowing you to intercept operations against AD and to extend the functionality. Such as Setting Passwords on new accounts etc.


    public override void AfterAddItem(object sender, DataCompareItemInvariant item, object identity)
        Trace.WriteLine("AfterAddItem->{0}", identity); 
        using(var entry = DataSourceB.GetDirectoryEntry(identity)) 
            var uac = (int)entry.Properties["userAccountControl"].Value;            
            uac = uac | 0x10000; //Password never Expires
            uac = uac & ~0x2; //Unlocked
            entry.Properties["userAccountControl"].Value = uac;
            entry.Invoke("SetPassword", "!password123");
            entry.Properties["pwdLastSet"].Value = -1;            


Dynamics CRM

Dynamics CRM Connector, supports Dynamics CRM 2015 and also implements Automation Item Events when Batch and Thread Count equal 1.


Dynamics CRM Fetch Xml Provider

A new provider for Dynamics CRM that takes a FetchXml Query as the source, this can either be a Saved Query in CRM or a hard coded Xml.


File System Provider

The file system provider now provides real-time feedback during the data load to allow for stop operation and also supports the Automation Item Events. File System can be configured to only return files that have been modified since a certain date.


Grouping Analysis Provider

New provider that can be used to group and summarise data, supports the usual set of aggregate functions.





The OData provider now supports Automation Item Events.


OpenXML Provider

New Provider for Excel using the OpenXML format eliminates the old 32/64bit issue with the previous excel provider that targeted the Microsoft Office OleDb drivers.



The Podio Items provider now supports Automation Item Events.



SharePoint providers ClientAPI and Online have been updated with new client libraries and support for the new Automation Item Events. This allows you to intercept operations against SharePoint to write your own implementation or other required functionality.


Simego Web API

The Simego Web API provider now can connect to Ouvvi to enable Read-Write operations against the Ouvvi Database to sync up Projects, Steps etc.



The SQL, OleDB and ODBC providers now support Automation Item Events. Also we have added Helper Functions ExecuteNonQuery, ExecuteScalar and UpdateSourceRow to enable simple write back scenarios.


Support for SQL Server LocalDB instances.


Values Service Re-Direction

When your project is running under Ouvvi you can now re-direct the Values Service to obtain values from the Ouvvi User Settings Store. It is possible to both Read and Write against User Settings hosted in Ouvvi via the Values Service.


Ouvvi User Settings

Creating Project Properties with the same name as Ouvvi user Settings causes the values of the properties to be updated at runtime from the Ouvvi User Settings.


Ouvvi Multiple Instances

You can now install multiple instances of Ouvvi on a single server, each instance is isolated from the others. This allows different versions of Ouvvi and Data Sync to be running on a single server to aid upgrade scenarios. Each instance of Ouvvi has a SQL DB, Windows Service, Data Sync install and private Connection Library. You can mix this configuration to suit your requirements.




Ouvvi Connection Library Re-Direction

Opening a Data Sync project hosted in Ouvvi will cause the Connection Library of Data Sync to be re-directed to the Ouvvi Server hosted version.


Ouvvi Data Sync Project View

You can now view the details and mapping of a project directly in Ouvvi without the need to open the project.




Ouvvi Data Sync Connection Mapping

You can now re-map the connections of a Project hosted in Ouvvi without opening the project in Data Sync.



17 July 2015

Data Sync and Ouvvi update

In the Latest Data Sync beta build (3.0.936) for Ouvvi we have enabled the ability to view the Data Sync project configuration right in the browser.


This allows for a quick overview of the project and the mappings. You can even switch the connection library connections right in Ouvvi.



14 July 2015

Multiple Isolated Ouvvi Instances

As of release 3.0.930 of Data Synchronisation Studio it is now possible to install multiple instances of Ouvvi on a single Server. This allows you to create a instance for say DEV, UAT and PROD on a single server. Or you could use this to break down a large Ouvvi instance into smaller Line of Business processes or similar.


Each instance has it’s own SQL Database, Connection Library, User Permissions, Ouvvi Service and version of Data Sync. This allows you to test new releases of Data Sync and Ouvvi without impacting a production instance.


Because of this change, Ouvvi is now distributed as part of the Data Sync install rather than a separate install. The install of Ouvvi is a manual process that involves pointing IIS to a directory that holds a copy of Ouvvi and running the in-browser setup and configuring the Windows Service.


We will be documenting the installation process shortly however if any clients would like to try this out please get in touch via our support desk.


We have a quick guide for installing a Developer Instance with IISEXPRESS and LOCALDB here https://www.simego.com/Help/Ouvvi/Install-Developer-Instance-of-Ouvvi-Automation-Server

10 June 2015

Dynamics CRM Campaign Rollup

This is an interesting use-case for Data Sync to be able to Rollup Contacts linked to Campaigns via Marketing Lists to be used with Marketo.


The idea being we need to add a custom text field to the Contact Record that will contain a list of Campaigns that the Contact is assigned to.


i.e. Campaign 1|Campaign 2


This involves a bit of FetchXML and C# code in Dynamic Columns to bring together.


First the FetchXML to Find all the marketing Lists and Campaigns that the Contact is related to.


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="list">
    <attribute name="listname" />
    <attribute name="type" />
    <attribute name="createdfromcode" />
    <attribute name="lastusedon" />
    <attribute name="purpose" />
    <attribute name="listid" />
    <order attribute="listname" descending="true" />
    <link-entity name="campaignitem" from="entityid" to="listid" visible="false" intersect="true">
      <link-entity name="campaign" from="campaignid" to="campaignid" alias="ab">
        <attribute name="name" />
    <link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true">
      <link-entity name="contact" from="contactid" to="entityid" alias="ac">
        <attribute name="contactid" />
        <attribute name="emailaddress1" />

You can use this with the FetchXML Data Sync Provider to return this data.


This provides the data in rows as normal we now need to roll this up to create the string with the Campaigns concatenated.




This Dynamic Column code, first reads each row storing it in a Dictionary against Email Address and using a Trick to throw away each row it sees. Then during the End() method we iterate over the Dictionary returning new Rows from the Data we collected from the FetchXML result.


#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Linq;

using Simego.DataSync;

/// <summary>
/// This module of code allows you to define some columns in code or Dynamic Columns
/// To Create new Dynamic Columns simply expose them as properties in this class.
/// </summary>
partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
    private Dictionary<string, CampaignRollup> campaignMembers = new Dictionary<string, CampaignRollup>(StringComparer.OrdinalIgnoreCase);
    private bool result = false;
    private CampaignRollup _current = null;
    public Guid? ContactID { get { return _current != null ? _current.ContactID : default(Guid); } }
    public string EmailAddress { get { return _current != null ? _current.EmailAddress : null; } }
    public string Campaigns { get { return _current != null ? string.Join("|", _current.Campaigns) : null; } }
    class CampaignRollup
        public Guid ContactID { get; set;}
        public string EmailAddress { get; set; }
        public List<string> Campaigns { get; set; }
        public CampaignRollup()
            Campaigns = new List<string>();    
    public override bool BeginRow()
        if(result == false) 
                var cr = campaignMembers[acemailaddress1];
                var cr = new CampaignRollup { ContactID = accontactid, EmailAddress = acemailaddress1 };
                campaignMembers.Add(acemailaddress1, cr);
        return result; // return false to skip row from results.
    public override void End()
        result = true;
        foreach(var key in campaignMembers.Keys)
            _current = campaignMembers[key];            
        _current = null;


This then provides us with a Data Set like this




Which can be easily synchronised back to the Contacts Entity updating a Field so that is contains the Campaigns.

13 May 2015

Values Store Service and Incremental Sync

Requires Data Sync Release : 3.0.912+


Data sync has had incremental Sync mode for sometime all this requires is that you set Incremental mode and return a subset of Data from your Source that you want to compare and Sync with your target.


The trick is how to easily update the filter condition on your source to automatically move on each time you sync. With Ouvvi you can use the project properties and access the last sync time, however outside of Ouvvi it’s a little trickier.


Introducing the Values Store Service this is a small Web Service hosted on the Web (you will need internet access for this) that can be used to Store, Retrieve and Update simple string values. This Service is a simple REST service and we have created a nice wrapper in Data Sync for you.




In order to implement the Incremental Sync with the Values Store service you would use Project Automation to get a value, update the source filter, synchronise and at the end update the value.


This is quite simple via the ValueStoreService object that exposes 2 methods GetValue and SetValue.


The API requires a guid ID value and a string Key value combined these must be unique.


The code below is an example of how you might use this, in Start() we get a Value from the Store if this is the first time i.e. there is no value in the store we return a default value of Today-1 Year.


We then use this value with a Helper Function GetFetchFilterXmlForModifiedSince which will return the CRM Fetch XML for us based on a DateTime value. We update the CRM fetch XML Filter property on the source with this and the Compare process starts.


After the Sync has completed and everything was ok we then update the Store Value with the Time and Date when the process started so that the filter moves on. At this point if you compare again you will see zero results since there have been no changes on the source and the source returns no records.


class ProjectAutomationOverride : Simego.DataSync.Automation.ProjectAutomationShim
    private string AppID = "{903A4B0A-586D-4666-95F4-EB858DBEF1F3}";
    private string AppKey = "AccountsSync1";
private DateTime SinceValue; private DateTime StartValue = DateTime.UtcNow; public override void Start() { SinceValue = ValueStoreService.GetValue(AppID, AppKey, DateTime.Today.AddYears(-1)); DataSourceA.FetchXmlFilterExpression = DataSourceA.GetFetchFilterXmlForModifiedSince(SinceValue); } public override void End(ProjectAutomationResult result) { if(result.Success && result.HasChanges) { ValueStoreService.SetValue(AppID, AppKey, StartValue); } } }


If you want to host your own version of the Values Store Service contact us for the details and assistance in installing the service and re-directing the Data Sync ValueStoreService for your version.

8 May 2015

Update To Excel Data Sync Provider

Requires Data Sync Release : 3.0.908+


Previously the Excel Data Sync provider was based on the Microsoft Excel Driver for OleDB this was Jet for XLS under 32 bit and ACE12 for XLSX or 64 bit. This had many issues the main issue being 32 and 64 bit support problems. This accounted for a good proportion of the HelpDesk Tickets.


Now we have a new provider for Excel the OpenXML Excel Provider and as the name suggests this is based on the OpenXML SDK.




This provider also supports DELETE Operations which is nice, not much more to say really simply connect to an Excel Sheet and go.

8 May 2015

Data Sync Automation Item Events

Requires Data Sync Release : 3.0.908+


We’ve been busy adding a new feature to Data Sync Project Automation that allows you to execute your own C# .NET Code during the synchronisation. This new feature is currently still in development build 3.0.908+ but available today in the latest beta build for you to try.


The events are as follows:-

  • BeforeAddItem
  • AfterAddItem
  • BeforeUpdateItem
  • AfterUpdateItem
  • BeforeDeleteItem
  • AfterDeleteItem


The before events allow you to cancel the operation by setting the Sync property false, you could then provide your own implementation.


The after events run after the item is committed and in certain providers will return the items identity in the target system. For example in Dynamics CRM this will return the Entity Guid ID value. In SQL Server the identity will return the value of SCOPE_IDENTITY on Insert.


We have also added some helper functions, for example the SQL Server provider now has ExecuteNonQuery and ExecuteScalar functions. This allows you to easily write back to the source record.


For example setting a field called LastSync to the current time.


public override void AfterUpdateItem(object sender, DataCompareItemInvariant item, object identity)
    DataSourceA.ExecuteNonQuery("UPDATE Northwind2 SET LastSync=? WHERE ProductID=?", DateTime.Now, item.Key);

This can actually be simplified via the UpdateSourceRow method


public override void AfterUpdateItem(object sender, DataCompareItemInvariant item, object identity)
    DataSourceA.UpdateSourceRow("LastSync", DateTime.Now, item);


Not all providers support the automation events yet the providers with support for Automation Item events are.


  • SQL Server
  • OleDB Database
  • Odbc Database
  • Dynamics CRM 2011-2015 Entity Items (Batch off i.e. Batch=1 and Threads=1)
  • Podio Items, Members, Contacts

11 December 2014

Export Active Directory User Group Membership

In this example we show how you can use Data Sync to create a Data Set from your Active Directory that returns all the Groups that your users belong to.


The idea is that we want a rows for each user and group like this below.




By default Data Sync will return the Group membership (Member Of) as a Comma separated List Like this Builtin,Remote Desktop Users,Simego Remote Desktop Users,Users


We need to use a little Trick in Dynamic Columns to override the Row loading and expand this list into multiple rows for each group copying the values across the rows.


Go into Dynamic Columns and use the code below to expand the Member Of column into multiple rows.



using System;

partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
    public override bool BeginRow()
            var parts = MemberOf.Split(',');
            if ( parts.Length > 1 )
                foreach(var s in parts)
                    var new_row = Table.NewRow();
                    foreach(var c in Table.Columns) 
                        if ( c.ColumnName == "Member Of" )
                            new_row[c.ColumnName] = s;
                            new_row[c.ColumnName] = Row[c.ColumnName];    
                    Table.Rows.AddWithIdentifier(new_row, ADPath);
                return false;
        return true; 

Since the DSID is no longer unique we need to use a combination of 2 columns for the Key we are using DSID and Member Of.




Now when you look at the results you get Rows for each Group and User.