A customer had a requirement to move data from an existing CRM system into Sage CRM and they wanted to use the Web Services interface for the migration.

The Sage CRM web service interface provides an excellent way of transferring data from another application into Sage CRM. There are also third party tools which can help with any migration which are listed within the Apps and Extras section. I have discussed importing data in a previous article "Some thoughts on Importing Data".

But I thought it would be useful to set out some additional observations.

The first thing that it is important to understand about the Sage CRM web service interface is that it is really only concerned with application data and only has a minimal set of methods for enquiring about Meta Data. And these are only schema discovery methods, that is methods that allow you to check whether a table or a column exists in CRM before trying to write into it. You can learn more about them in the article "Creating Web Service Applications that handle unknown tables and columns". So the web services by default can not handle any information that control the Graphical User Interface, which is the case with the .NET and COM APIs.

The next thing to consider are the data models. You will need to take to take a long hard look at both the source system data model and the Sage CRM tables.

You'll need to consider each of the different fields and how they map across.

• How is the data modelled?
• Are the tables equivalent or will you need to split one source record over several tables?
• What are the data types used?
  • Are you going to be moving data that is held as a string to move it in an integer?
  • Will you need to make sure that selection list options are constructed first? See the article "Selection Lists in Web Services".
  • Will you want to work with selection list that are decribed as strings or as an enumerator - it is probably easier for you to move unknown data into Sage CRM when the selection lists are configured to expect strings.
  • How will you handle dates? Inserting with UTC values? See the article "Setting Datetime fields to null via the SOAP webservices interface".
  • What is the data length? There is a method in the Web Services that allow you to change the length of a field but it is probably better to have made sure data lengths are correct before the upload.
• Will you be moving historic data? Sage CRM allows for old long gone users to be carried over into Sage CRM using a method known as AddResource (which actually adds the user a disabled user). See the article "Adding Users via Webservices".

If you have added a custom table into Sage CRM its columns must follow the correct naming conventions. e.g. Column prefix must be used (xxxx_whateverid) and field must not use an integer as the first character after the prefix (xxxx_1address). See the article "The Importance of Column Prefixes in Tables Exposed via the Web Services".

You will have to think about the relationships of the data that is being inserted.

The Web Services have objects that are strongly typed. As an example of this you can add a custom table into Sage CRM, for example 'Project', this will be described in the WSDL. All of the columns and the data types are able to be completely controlled by you. But consider the Company or Person structures in Sage CRM. A company will have a default contact and possibly multiple contacts associated with them. The same goes for associated address, phone and email data.

These exist in parent/child relationships and so we would need to programmatically handle those transactions. See the article "How are transactions managed over multiple webservice requests?".

There is a choice of methods when it comes to inserting the data. We can either use the AddRecord() or the Add() mechanism. AddRecord() allows you to specifty the columns of a new record being inserted and does support the idea that you can add a array of records all of the same type. The Add() mechanism allows you to add a table and the defined array/collections that belong to the hard typed definition of the 'entity' in the WSDL. The child person records of a company entity are handled in the people array and the child addresses are handled similarly.

But you can not add new child arrays to the company entity. There is no way of changing the WSDL definition of the company entity in that regard. So if you wanted opportunities or projects to be imported as children of a company within the company entity then that is not possible. You would have to add those child records in a second transaction.

You would of course face the challenge of maintaining referential integrity too.

Note: Some columns in the database maybe suppressed deliberately in the web service interface. You will need to check the WSDL definition in this case.

You will have to think about is the transaction size and how to handle failure. The smaller the transaction size the better. I think it is easier to handle errors if the amount of data involved in any transaction is kept to the minimum.

Other things to consider...

You will need to think about the processes that any of the imported data is in. By this I mean workflow. The Web Services interface will allow the data item to be added to Sage CRM but it does not have methods to attach a record to a workflow. So you will need to have designed the workflow in Sage CRM to take into account the state of the data of these unattached records - so you can continue to process them using the Sage CRM graphical workflow.

If there are documents and files that need to be uploaded into the system the Web Services interface does not have any methods to handle file uploads and addition into the document library, therefore you will have to handle the upload of documents and files in a secondary phase of the data migration.

You will need to consider the overal performance of the application. See the article "The Design of Web Service Applications and Performance".