DatabasePopulation
From OpenEMM Wiki
For some users of OpenEMM subscribe/unsubscribe is not a binary field. In my company's situation we have dozens of custom 'profile' fields which we use to indicate whether the user is subscribed or not to each individual list. Maintaining this list using the interface provided by OpenEMM is not practical. To further complicate, the server which maintains our customer lists is located on a different subnet on SQL Server 7.
Below is a informal description put together by my co-worker which described the basic technique that we used to update our OpenEMM database automatically each day. It has run for about 3 weeks now and seems to work very well. It creates new users, collects bounced users (for re-collection) and generally makes our life very pleasant. Its rudimentary (I'm a programmer not a technical writer) but it should serve as a guide to any experienced dba. Just the fact that it can and has been done would comfort most!
I have made a DTS package to do the update, insert and delete from in this case MS SQL 7.0 (although the basic principle is the same regardless of the platform) I am not trying to be very detailed but to cover everything so it is helpful in first glance. Though I’ll be happy to assist further if needed.
First, we need to install MySQL driver and then simply create an ODBC connection to your OpenEMM database and use it in DTS package. Although, I could manage to update a record on a table on fly to MySQL but it hangs very often and it is much better to create a delta table of changes, push it to MySQL and apply the changes there. I mean I move the table customer_1_tbl plus the field user_status from the table customer_1_binding_tbl to our SQL Server database.
SELECT DISTINCT c1.*, c2.user_status
FROM customer_1_tbl c1
INNER JOIN customer_1_binding_tbl c2 ON c1.customer_id = c2.customer_id
WHERE c2.mailinglist_id = ‘your mailing list id’;
Then I will populate another table, I called it OpenEMM_customer_1_tbl_changes that has the same columns as customer_1_tbl plus a field ‘changes’ as an attribute to know what kind of change I need to apply later to OpenEMM database. This is done by DTS “Transfer Data Task” object. Please note although the table customer_1_tbl does not have a key on email but it is wise to maintain it as a key so have a separate update attribute for this column. You can use this for when you are updating the column user_status in customer_1_binding_tbl, if you are switching back a bounced email to active too.
And then move the table OpenEMM_customer_1_tbl_changes to OpenEMM database again using “Transfer Data Task” object and perform your updates. This way also you can archive your changes in OpenEMM database for future references.
Note1: Update the date fields as well.
So in this manner, think of MySQL like another SQL Server and replace “Microsoft OLE DB provider for SQL Server” object by “Other Connection” object. When you have the connections established, you can transfer data between them and run any query. All of this will run on SQL Server side.
Note2: Update or Insert scripts should be written according to the database we connect to. MySQL differs in scripting to SQL Server.
There have been three tables to update customer_1_tbl, customer_1_binding_tbl and customer_1_seq. You also need to know some of the columns definition and their values. Most of it, we guessed and they are so far OK. For example the column user_status in customer_1_binding_tbl can have value 1 for Active, 2 for Bounced and 3 for Opt-Out by Admin. And I think, they are not that hard to guess.
The last challenge was scheduling the job. ODBC driver does not like SQL Server account so you’ll need to create a batch file and inside it trigger the DTS package and have a windows job task scheduled to run the batch file. This will use windows account and no doubt, the windows account should have administrator privilege to SQL Server so can trigger the DTS package.