What I need to do is some data between 2 databases. The source can be anything (comma separated file, xls file, any database, ...), the destination is MS SQL Server.
I do not need to sync all data, I just need to sync particular tables.
Example:
I need to sync accounting Software (runs on PostgreSQL) CUSTOMERS table with CRM (runs on SQL Server).
Some problems this tool should be able to face:
1) Accounting software customers table has 1 field that is not mapped in crm customers table. (In this way I want to map this extra field to the field CUSTOMERS_CUSTOM_DATA.EXTRA_FIELD)
2) Having some rules (like sync only customers whose code is between 10000 and 99999)
3) Allowing to perform some post insert tasks (for example I am using manually managed seuqences for the tanble IDs, so after inserting 10 records I need to add 10 to the sequence)
4) Having an exception handling mechanism so if something is wrong it can wither call a sql server stored procedure (that I already have and it will send an e-mail to me) or simply send a message to notify that something was wrong in the nightly sync.
5) Be easy to schedule when to perform data sync (hourly, daily, INCLUDING MANUAL)
6) Perform data conversion: if Surname field in source table is varchar(20) and in destination table is varchar(15) I want to explicitly say "perform a truncation".
7) Have different rules for insert or update. For example in the source e-mail field is not present, but I want to populate it in the destination I decide to perform this operation on insert only, not on update. (for example as I insert a new customer I want to populate the e-mail field concatenating name and surname, but then I want to let the users to modify it, this first insertion is just to simplify data entry, but then this particular case will be handled manually. So I want to say (on insert populate e-mail field, on update don't do anything with email field)
8) In case of delete in the source db don't delete on the destination but only change the varchar(10) STATUS to DELETED.
Note: I know that Integration Services will be perfect for this, but I must support the Express Edition, so SSIS is not an option.
I created a bunch of scripts and scheduled stored procedure that at present do what I need, but it is very hard to maintain and the total lack of a GUI makes the work much slower. I remember having seeing TALEND time ago, maybe that tool is also the answr I need, anyway I need to provide a quick answer to management, so I have now no time to investigate all the tools on the market, and I would prefer to have a suggestion from an expert.