I have written several articles in the past about the importation of data into Sage CRM. This article then is a 'header' record that attempts to bring these different thoughts together.

It is certainly not unusual for a partner to occasionally need to design a custom import mechanism into Sage CRM. And this source data can originate in a variety of original forms such as spreadsheets, text files but more typically in other databases.

When data is imported into Sage CRM from another system it would typically need to be transformed to match the Sage CRM data model. For example an entity representing a customer would need to be split over multiple tables such as company, person, person_link, address, address_link, phone and email.

You would need to make sure that you are familiar with the Sage CRM data model. A copy of the application data model is available in Visio format with the standard documentation on the installation CD.

Other articles that discuss different parts of the data model can be found on this website.

The Key Attribute Tables
Reports, Saved Searches and Groups

Importing data is the one off process of bringing external data into Sage CRM and as such it is very much related to the concepts involved in creating integrations with 3rd party applications.

There is an article that discusses the different approaches that can be taken by a developer creating an integration with an external system using Sage CRM. See the article called "Integration with External Systems and the Synchronization of Application Data"

Techniques Used for Data Importation

The different API options and architecture available to developers working with Sage CRM means that there are 4 approaches can be used in theory.

1. Web Services
2. COM Based Mechanisms
3. .NET API
4. Direct Data Interaction

Using the Web Service Interface

If you are going to carry out an import of data using the Sage CRM WebServices Interface then the first thing to note about the interface is that is both flexible and very restricted. This paradox is down to the fact that although the interface allows you to expose additional custom entities and new columns in the interface nevertheless the main entities such as company and person work in very strictly determined ways.

Entities such as company or person are described in the WSDL in such a way as to allow the relatively easy importation of data that CRM can then split across the tables in the data model. There are plenty articles on the use of the web services on the website and there is also a sample C# project that includes a section of code showing how a company record can be added into Sage CRM.

What we can not do with the web services is to change them to add new types of relationship into the definitions. The child person records of a company entity are handled in the people array and the child addresses are handled similarly. But we 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. We would have to add those child records in a second transaction. We would of course face the challenge of maintaining key integrity too.

You can not interact with tables that are not exposed to web services so that fact can place restrictions on what you can import.

The good news about the web services interface is that the sessions use a user logon and follow the security restrictions of that user, and also the importation of the data would potentially trigger TableLevel scripts.

COM Based Mechanisms

The COM based techniques are either

• ASP application extensions
• External COM applications

ASP application extensions

This is where the import mechanism forms part of the Sage CRM user interface. The user would choose to carry out the import of data from menu or tab options. The code that drives the import would use ASP pages and the objects of the Sage CRM COM API.

External COM applications

Here the import does not form part of Sage CRM but is run as a script/program external to the user interface. But the code still uses the objects of the Sage CRM COM API.

Both of these mechanisms will allow you to use either the Record object or the Query object. The Record object is much better for the importation of data as the following articles discuss.

Further Reading

• Using Code to Import Data in a on Premise Installation
• Some thoughts about Importing Outlook Contacts
• The eWareQuery Object (COM API)
• How can I easily import a large list of Selection list values into CRM?

.NET API

The .NET API is only designed for the creation of application extensions so there is no option with the .NET API for creating an external application to carry out the import of data. You can not create a windows .NET application that used the Sage CRM .NET API as the API is designed only for use in the context of the Sage CRM web transaction.

The .NET API includes a Record based approach and a Query based approach. For handling inserts into Sage CRM tables as the COM example above, the Record based approach is preferred.

If you want to build a .NET application then you would need to use the web services interface to carry out the data interaction.

Direct Data Interaction

This is the choice you might make when you just want to bring data from one database table into another database table.

You may think that you can take advantage of the SQL Identity fields when inserting into CRM tables. You would have to consider desired interaction with TableLevel InsertRecord, PostInsertRecord event functions. Then there is automatic behaviour with Notifications and escalation rules (and any needed insertion in the escalation table).

The manual insertion of records also by passes security right checks and any automatic territory assignment. You would need to handle all that too.

It is not part of the documented API so could change between versions.