Keeping AwareIM data in sync with data from a MS-SQL Server

Looks like a common scenario, but there are some pitfalls ...

Scenario

• We have about 2000 customer records in a MSSql database that are required in a AwareIM solution. Updates don't happen very often (almost never) but a few customers might be added every day.
• So the plan is to export the data from the MSSql server using a scheduled task and to import them into AwareIM by a process that gets called by the user when required.

Problems

1. AwareIM's IMPORT function doesn't support adding AND updating data from the same file (correct me if I'm wrong)
2. We need to do some field assignments like Customer.Name1 to Customer.Company
3. AwareIM needs the header in the form Table.Field which causes troubles to Microsoft Software because they don't like periods in field names at all.

The MSSql Server Part

dataexport.cmd (gets executed every 15 minutes)

"C:\Programs\Microsoft SQL Server\80\Tools\Binn\DTSRun" /~Z0x7A......54134EFA
cd \data\export
type header.txt > dataexport.txt
type data.txt >> dataexport.txt
pscp -pw xxxxx dataexport.txt admin@appsrv.local:awareim/importexport/dataexport.txt

1. Using the Data Export Wizard on or MSSql 7.0 server I created a package that writes the whole table to data.txt
2. Creating a scheduled task on the SQL server's Enterprise Manager that starts this package worked but every scheduled run ended in an obscure error message telling me that a file can't be found. So I used the command line from the scheduled task and put it into a .cmd file that executes fine using Windows' own task scheduler %-)
3. As stated above the data file needs a header line in the form table.field that can't be produced by Microsoft. So the export must only contain the data with no headers. The headers come from a manually created additional file called header.txt.
4. Copy could also be used to join these 2 files together but after that the last line contained a strange character that stopped AwareIM's process. Type works fine...
5. pscp  is used to copy the file via SSH to our linux appsrv

The AwareIM Part

1. Create a table (or Business Object) dataimport that contains the same attributes than the data.txt
2. Now it's time to create a process that:
3. Imports data.txt into dataimport
4. Activates a rule in dataimport that:
5. checks if the record is new in this case a new customer has to be created
6. checks if the record is changed in this case the customer gets updated
7. clears dataimport

That's what the following process does

IMPORT dataimport FROM SystemSettings.importfile
FIND ALL dataimport
UPDATE dataimport

First it imports the data. After that it finds every record an executes an update on this record. And UPDATE causes the rules of dataimport to get active.

The rule of dataimport contains:

If NOT(EXISTS customer WHERE (customer.KHKID=Thisdataimport.ID)) Then
CREATE customer WITH customer.company=Thisdataimport.Name1,customer.KHKID=Thisdataimport.ID, .......

which checks if the current imported record already exists in customer. If not then the customer is created

If you say that the process could also be made like
IMPORT dataimport FROM SystemSettings.importfile WITH VALIDATION
then be warned ! On a P4 2,4GHz with 1 Gig Ram the whole process ran about 1 hour ! Whereas the solution shown above finished it's work after about 30 seconds. And yes, the data is the same !