We have an existing Microsoft SQL 2008 database that we would like to integrate with MailChimp. The requirements are to write a connector that extracts the appropriate information fields from the SQL database (using Stored Procedures developed by us) and updates the MailChimp database on a daily basis. This will be a two way street.
We will provide stored procedures to do the following:
1. Zero parameter stored procedure that will gather information from our database and provide a dataset. This dataset will then need to be verified against MailChimp's Database. If there is an existing list then update the contact information within the list. If there is no existing list then create a new list with the dataset. The following dataset will be returned initially: (*** needs flexibility to add more columns in the future. ***)
2. One parameter (email) stored procedure that will update our system's mailing lists for users that unsubscribed.
3. One parameter (email) stored procedure that will update our system's mailing lists for users that bounced.
The program will need to do the following:
[url removed, login to view] Subscribe users with data from sql 2008 database with a column (e.g. Email, FirstName, LastName, Category, Business, etc.) *** needs flexibility to add more columns in the future. ***
2. Unsubscribe users by passing an email parameter into the stored procedure mentioned above.
3. Unsubscribe bounced users by passing an email parameter into the stored procedure mentioned above.
We will create a table within our system (EmailLists) that will contain the following:
-Index - int identity
-MailChimpID - varchar(100)
-ListName - varchar(100)
-EListSproc - varchar(100)
-UnsubSproc - varchar(100)
-BouncedSproc - varchar(100)
-Active - bit
-LastListSync - datetime
-LastUnsubSync - datetime
-LastBounceSync - datetime
The program must be able to scan the table and find programs that have not been synced within 24 hours per task. Any program meeting this criteria must then use the parameters stored within the table columns to call the appropriate stored procedure per task. The appropriate sync column timestamp must be updated once the task has been completed:
The technology we currently use is:
- .Net Frameowrk 4.0
- Console Application
- Sql Server 2008
- Visual Studio 2010