Migration Web Forms For Marketers database data from SQLite to MS SQL

This is my first blog post and, of course, first Sitecore thing which I would like to represent for community.
I hope it would be useful information for those who faced the same problem.
So, lets start.

Last days I had task of correcting and upgrade the Web Forms For Marketers module in one of projects what I worked on.
Everything was going to be all right and there is nothing to foreshadow the problems in that task, but...
I've faced one thing which, I thought, was not serious. When I recognized that I should reconfigure WFM module to use MS SQL database for storing data I also understood that I have to migrate the old data from SQLite data storage to MS SQL one (Unfortunatelly old installation of WFM on the project used the SQLite).

I've tried to find some ready solution for that purpose, but haven't found anything. Strange, I was sure that such task isn't trivial but I hoped that somebody has already caught such problem and shared the experince.

I tried to find some simple and useful tool for converting between SQLite and SQL but I couldn't find anything what satisfies my requests.
After several hours spent for googling, I stoped and said: "Hey, man - why do you waste the time, the WFM database is very simple and you just need to copy the data from 3 table and that's it!!!".
I decided to make some very simple application and copy the data from SQLite to SQL programmatically using ADO.NET. All what I needed is to move the tables in right order:
  1. "Form" table.
  2. "Field" table.
  3. "Pool" table.
 It needs for avoiding conflicts in foreign keys among tables.

The tool is simple ASP.NET web form application with code behind file. There is no need to compile it.

In order to use tool copy the files to website root and fill source(SQLite) and target(SQL) connection strings text boxes.

As we can see from code below it just selected all data from SQLite table and put them row by row into appropriate SQL table.

So, I can just enter source and target connection strings, press the button and say good bye to SQLite data storage for my WFM data :).

The tool works for databases among Web Forms For Marketers module versions from 1.1.0 rev.091002 to the latest one (for this moment it is 2.1.0 rev.100806). All these versions have the same database format.

Hope, it will be useful. You can download the tool here

Comments

  1. Hi Ivan, I have a question regarding WFM 2.3 (for CMS 6.5). Is there any API reference guide on Webform for Marketers module on 6.5. I can not find any documentation or reference about this in sitecore650API.chm. Particularly I am looking to programmatically insert form and get the count. I appreciate you reading this.

    ReplyDelete
  2. Macrosoft, Thanks! Did you mean MS SQL?

    ReplyDelete

Post a Comment