With Data Synchronisation Studio you can now update the Manager attribute with the AD Provider if you modify the Provider configuration slightly.

By default we parse out the Manager field so that it’s more suitable for input to SharePoint or something. However you can now switch off this parsing to get to the raw AD attribute value.

To update the manager you need to supply the full Distinguished name value of the AD Object you want to set which would be something like this below.

CN=Sean Cleaver,CN=Users,DC=xxx,DC=xxx,DC=com

Disable the TryParseValue and ReadOnly properties of the Manager field in the Property Collection on the AD Provider.

Here I created a Dynamic Column to return the Distinguished Name of the Manager to set against the AD Users.

Now we just create a simple Schema Map mapping the X_Manager to Manager and using DSID as the Key column.

Now you just need to compare the data and synchronise the changes.

This post is an example of how you can use the Data Sync RSS Provider to create an Exchange Rate RSS feed. You can use a similar approach to create an RSS feed from virtually any kind of data.

First we connect Data Source A to the FX Rate provider.

We want a couple of additional columns for our RSS File so we create them using Dynamic Columns.

We then connect the Target Data source B to the RSS File provider, enter a name for the File and click OK.

We then create the Map between the FX Rates and the RSS File like this.

Schema Map

Once you sync up the Data you will have a nice new RSS File that contains the current currency exchange rates from Yahoo.

This (no code) post shows how to use Data Sync Lookup Columns to import Accounts and Contacts from SQL and keep the relationship between Account and Contact.

Our SQL Database consists of 2 tables “Account” and “Contact” these 2 tables are linked via a Foreign Key Contact.AccountID –> Account.ID.

CRM doesn’t work with Integer keys it uses Guids for the Primary Key on each record so whilst you could update your Database with Guids for this import I will show you how to do this with Data Synchronisation Studio without making any changes to your Database or CRM.

Step 1 - Import your Accounts

This is an easy Data Sync project where you simply import your accounts table into the Accounts entity mapping name to name and id to accountnumber.

Project in Data Synchronisation Studio

Schema Map

We now just sync the changes into the CRM Account entity.

Step 2 – Import your Contacts

This step requires mapping the Contact table to the Contact entity in CRM mapping the ID value from the SQL Table to the ExternalUserIdentifier column in CRM.

Project

Schema Map

We need one more trick to automatically associate the Contact with the Account record in CRM. For this we define a Lookup between AccountID and the accountnumber in CRM.

Select the AccountID column in the Data source window and click the “Add Lookup” button.

Next configure the Lookup Data source to connect back to the CRM account entity.

Next define the Lookup relationship by selecting accountnumber in the Target Column field.

Now you can use all the columns from the related Account CRM entity in your project.

We now need to map Lookup1_accountid to parentcustomerid to create the relationship in CRM.

and that's it you can sync up the data and see that your contacts are correctly related to your accounts.

Considering the impact on delivery dates, testing, training, regulatory compliance and budget, it is always amazing how little focus there is on migration. There are many many traps to fall into when implementing a new system but migration is one stick that the underlying business will beat you with.

1. Your project plan has one task that says ‘Migrate Data’.

This is very easily done as either the team developing the new system or the vendor, can’t estimate the effort so it is just left as a single item. Don’t do it. Do some analysis that will at least give you a consideration of risk, effort and cost. Without these, managing the expectation of the business will be impossible.

2. Not using an automation tool to migrate data.

In the past I have conducted migrations based on SQL scripts, batch files, custom code etc. Quickly you realise that there are real benefits to automation. For example the new system has 5 new fields in a table. Rather than going back and redeveloping the migration code, you just point the tool at it and click update. 1 hour saved and no errors introduced, commit it to source control.

3. Migrating old data that might better be placed in a reporting system.

You should always consider that some data might be better placed in a reporting or document management solution. If you have a 'end of year' balance brought forward into a new system, do you need all of last years transactions or can you just lookup a report showing them?

4. You let the vendor leave out migration or worse make it your entire problem.

Migrating data is risky and can be expensive; your development team knows how their current system works better than anyone. Make sure you have some migration commitment from the vendor before agreeing the deliverables and costs.

5. Assume that the migration with be straightforward because the old system and the new system do the same thing.

The architectural requirements of systems mean that they may have completely differing internal structures and security models. These factors influence the effort of migration by factors of 10. Even simple upgrades between versions can be the same as moving to a different vendor’s.

6. Underestimate the effect of the new security model on migration.

Security models are there to make sure that data is not viewed or manipulated by an unauthorised individual. During migrations it is often necessary to circumvent or assume the identity to extract and insert data. The developers of the current systems could make it extremely difficult for you to move data by using encryption, logic in the user screens and even more esoteric (smart at the time) tricks.

7. Thinking you can sort out the data quality once it is in the new system.

Often a driving reason the move to a new system is that it has better data quality control. For example, we often see telephone numbers saved as ‘TBC’ in an old system and the new system will only allow number to be saved. A significant phase in migration is cleaning the underlying system so that clean data can be stored easily into the new system.

8. Underestimating the involvement from the business to clean the data.

As with 7, it is important to realise that users will have to assist in cleaning data. You can make this easier by creating lists of broken records and request rules for your automation tools to follow.

9. Not realizing the impact of month/quarter/year ends on the migration.

Many accounting systems require journaling and retrospective adjustment. Additionally, when picking a date for migration, make sure the business is available and not buried in month end, quarter end, tax year-end or year end.

10. Not documenting the migration process so no one knows where the data came from!

When migrating data it is often necessary to transform the data several times before it finally resides in the new system. Often a client will ask, why is the address used and not the billing address from the old system? You need to have a method of tracing the source to target and often you automation tools can help to generate documentation.

At Simego we have been involved in assisting clients who have undertaken system migrations. Above is a guest re-post from :www.infuse.je/blog/top-ten-system-migration-traps as they have used many of our products especially Data Sync Studio :http://www.simego.com/Products/Data-Synchronisation-Studio

The latest version of Data Synchronisation Studio has gone live today, this release has had major re-work to allow loading of much larger data sets and to significantly reduce the memory usage of the application. Our reliance on the .NET DataTable object is now gone and we created a new object to store the data which uses fast disk based storage instead. This also allowed us to to improve Dynamic Columns so that DBNull.Value is now gone and is just NULL and you can now use Nullable<T> in you Dynamic Column properties.

For example loading an comparing Data Sets of Millions of rows so now possible. For example below is a screenshot of Data Sync loading and displaying over 18 Million Rows from a CSV file.

We also now have a new Social Panel on the Start Page so that you can see our latest Blogs and Tweets right from within the application.

This release includes our new Data Generator Provider which with Dynamic Columns allows for the generation of Sample Test Data or for you to connect to other data sources we haven’t created a provider for. With the Test Data Generator you can further transform your data such as creating Pivots etc.

If you have an RSS Feed you want to use as a Data source with Data Synchronisation Studio it’s really easy to use the XML File provider to read this feed and present it as a Data Source.

To configure the Data source you need to know the structure of the XML and a little XPath to define the Columns you want to return.

First enter the URL to your Feed for example http://www.simego.com/Blog/RSS

This is a standard RSS feed so each item/row is within an item element in the Xml therefore we set the XmlItemElementXPath to be //item

Next we need to define the columns to return, as the RSS is quite simple we just create columns for Link, Author, Title, Description, Source and PublishDate that map directly to the Xml element name in the XPathExpression field.

And that’s it now you have an RSS feed as a Data source that you can use to synchronise into some other system.

Download Example Data Sync Project

Overview

This article describes how to import a file such as CSV into a new SQL Server Table with Data Synchronisation Studio

Data Synchronisation Studio can also open files from :-

• CSV
• TAB
• XLS, XSLX (Microsoft Excel Files)
• MDB (Microsoft Access Database Files)
• SDF (SQL Server Compact)
• XML
• + many more

Step 1 - Open the CSV Data File

Choose the Connect Data Source on Data Source A in Data Synchronisation Studio to open the connection dialog. Locate the CSV File provider and select it. Then enter the path to the CSV file in the Filename property.

Once Data Sync loads the CSV File to discover the columns in your file you will be presented with the schema and a default schema map containing your columns.

You can now preview the data to check that it loads ok.

Now we need to adjust the schema to include the columns we want and setup the data types. We adjust the map like this below where we define the ID column as a Key and set the Data Type as an Integer.

Step 2 - Create SQL Database Table

We now use the Create SQL Table Wizard from the Tools menu to create a SQL Table to import the data. You can also connect to an existing SQL Table or SharePoint List etc.

Step 3 - Import the Data

Now when the Table is created the Schema Map is mapped automatically ready to compare and load the data.

Selecting the Compare A->B toolbar option compares the Data and creates a Change Set to apply to the Target. Simply click Synchronise and the data will be copied into the SQL Table. You can now save the project and quickly and easily run it again at any time.

This problem can occur when you "Refresh Server" and the process does not complete properly or you end task the application during some save operation of the core configuration files.

The application stores a cache of your database list and the server list in XML documents under your profile

On Windows XP

C:\Documents and Settings\<ProfileName>\Local Settings\Application Data\Simego\SQLToolsExplorer

On Windows Vista/7/2008

C:\Users\<ProfileName>\AppData\Local\Simego\SQLToolsExplorer

The quickest way to resolve the issue is to delete all the files in this folder however you can resolve it manually by checking that the XML Documents are not corrupt. Generally you will find that the file is missing an end tag.

You can safely delete any of the files however if you delete the settings.xml file you will need to delete all the server database cache files as well and then re-connect all your servers.

TIP: If you connect to a Server on a Shared Host and your database list is large then you can edit the Database Cache List to only display your database(s).

It’s possible to setup a near real-time bi-directional SharePoint List replicator between different SharePoint Sites/Farms etc with Ouvvi and Data Synchronisation Studio. Additionally there is nothing to install into your SharePoint environment so it can also work against hosted SharePoint or SharePoint which is locked down by your admins.

Ouvvi 2.0 has a new Trigger for SharePoint Lists which fires whenever items in the list change therefore if you configure a bi-directional Data Sync project and load it into Ouvvi and assign the triggers to the project you can build a replication system.

Here we define a new project in Ouvvi and create a single step which is the 2-Way Data Sync project. (You need to know how to build the 2-Way project with DSID replication columns).

We then define 2 triggers to query each list for changes, by default the triggers are executed every 30 seconds so you should expect the project to be kicked off within 30 seconds of a change (this is what I mean by near real-time).

You then assign the 2 triggers to the project so that whenever each one fires the project is started.

To prevent the project running sync jobs in parallel we define the project as Single Instance this way each start request is queued and only one instance is ever running.

You can also see in the logs when the sync runs how many items were changed.

And view real-time reports of the execution process.

Google provide several public holiday feeds such as this one here

http://www.google.com/calendar/ical/en.uk%23holiday%40group.v.calendar.google.com/public/basic.ics

Now with the Data Generator Data Provider in Data Synchronisation Studio 2012 and Dynamic Columns it’s easy to turn this into a Data Source.

The first step is to connect Data Source A to the Data Generator.

The Data Generator simply returns a number between 1 and the number you set in the properties.

All the magic happens in the Dynamic Column code where we override the Setup() method and load the Data from the Google service into a List of Events. Then for each Row Data Sync calls BeginRow() and we simply return whether there are any more events and return the calendar data as properties of the Row.

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

class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{    
    private List<dynamic> Events = new List<dynamic>();
        
    public DateTime StartDate { get { return Events[ID-1].Date; } }
    public DateTime EndDate { get { return StartDate.Add(new TimeSpan(23, 59, 0)); } }
    public string Title { get { return Events[ID-1].Title; } }
    public bool AllDayEvent { get { return true; } }
    
    public override bool BeginRow()
    {        
        return ID <= Events.Count; // return false to remove row from results.
    }

    public override void Setup()
    {
        var request = System.Net.HttpWebRequest.Create("http://www.google.com/calendar/ical/en.uk%23holiday%40group.v.calendar.google.com/public/basic.ics");
                
        using(var reader = new StreamReader(request.GetResponse().GetResponseStream()))
        {            
            DateTime date = DateTime.MinValue;
            string title = null;
            string line = null;
            
            while((line = reader.ReadLine()) != null) 
            {                        
                if ( line.StartsWith("DTSTART") )
                    date = DateTime.ParseExact(line.Substring(19), "yyyyMMdd", CultureInfo.CurrentCulture);
                
                if ( line.StartsWith("SUMMARY:") )
                    title = line.Substring(8);
                
                if ( line.StartsWith("END:VEVENT") ) 
                    Events.Add(new { Date = date, Title = title });
            }
            
            //Order the Events by Date
            Events = Events.OrderBy(p => p.Date).ToList();
        }
    }
}

And there you have it, Calendar entries from a Public Calendar feed that you can import right into a SharePoint List.

Download Sample Project File