A customer uses an external system to generate marketing emails to contacts. These contacts exist as records in their accounting system that is integrated with Sage CRM. This means that the contact exists in both Sage CRM and the Sage BMS but because the emails are sent from a third party system integrated only with the Sage BMS and not with Sage CRM they don't have a record in CRM that the email was sent to the contact.

They receive the CSV files with opt-outs and bounces and they want to upload these results into Sage CRM to flag the company/person accordingly.

How can this be done? Importantly how can it be done in a way that is safe, reliable, efficient and upgradable? It needs to be simple and easily maintained.

The first thing to recognise is that Sage CRM's marketing features include the ability to export records for email campaigns to be sent by an external system.

This inbuilt behaviour assumes that we start the mail process by exporting a CSV of contacts from Sage CRM. That's not what is happening in this customer's case but it is important to realise that marketing workflow is customizable and offers scope to be able to not only export records but also to record communications against those records.

You can read about this in the article Campaign Activities, External Mail Merge and Telesales

But let's assume that we really do need to create a customization.

I don't have a strong view about the design except I think the data does need to pass through the application lay of Sage CRM. This is to make sure that any table level scripts or escalation behaviour are triggered.

Let's assume that the list of records, each with the Company ID and the note that needs to be converted into a communication are all contained in the CSV file.

The program that reads these records can either read the CSV file directly into memory or you could upload it into a table using the SQL Server Import & Export wizard or whatever.

Either way, we would then need to process each row.

You have a choice of using the Web Services - see the article: Creating a Task in Web Services

Or .NET - see the article: Creating Appointments and Tasks in the .NET API

Or even the COM scripting that can be used using an external file or ASP page. See an example in the article:Moving files into the Library using the serverside COM API.

You can choose the API that is best for your circumstances but let us now assume you have a list of records that need to be processed and turned into completed tasks held against the company.

Bear in mind that binary processing (COM) is faster than Web Services. Classic ASP pages (even self-service) and .NET projects need to be called from a web transaction.

The COM API can be invoked from windows script files run on the server. See the article: Instantiation of the COM (eWare.CRM) object.

Whatever way you choose the process would look something like:

1. Open dataset (FindRecord) from import table.

2.Read the record from the dataset.
- If mandatory data missing from any field.
-- Copy record to 'error' table with a message.
-- Mark record for deletion in import table.
- If the foreign key (Company ID) is not valid.
-- Copy record to 'error' table with a message.
-- Mark record for deletion in import table

3. Create Communication Record.
- If the creation of Communication record fails.
-- Copy record to 'error' table with a message.
-- Mark record for deletion in import table.
- If creation of Communication record succeeds.
-- Write into Log (new Communication record ID).
-- Mark record for deletion in import table.

4. Delete records in import table flagged for deletion.

Note: I have assumed that you will want to be able to recover from errors so I suggest you capture rejected records in an 'error' table. I also assume you would want to have proof that the communication record was saved so I suggest you write into the logs.