13

I have a database with more than 32 million records, I have to migrate it from SQL Server to Sqlite.

I have tried SSIS (SQL Server Integration Services) with the help of this article http://dbauman.blogspot.com/2009/03/connecting-to-sqlite-through-ssis.html

but the process is very very slow, how can I migrate this data?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Saw
  • 6,199
  • 11
  • 53
  • 104
  • How much time does it take for you to dump the data into Sqlite using SSIS ? By enhancing the performance of DFT ,you can some how increase the speed – praveen Jun 09 '12 at 11:17
  • 1
    In the article ODBC is used, which usually is a poor choice if you have performance in mind; it just adds another abstraction layer. I found SQLite to be quite fast (I don't have any statistics at hand) when executing loads of prepared INSERT commands within a transaction, I never worked with SSIS unfortunately. – C.Evenhuis Jun 09 '12 at 11:23
  • 10000 records takes mote than 30 minutes – Saw Jun 09 '12 at 11:29
  • 2
    Just give a try :-If you have indexes in your sqlite table then try to drop them and load the data and then rebuilt it again – praveen Jun 09 '12 at 11:36

2 Answers2

19

There is a C# utility to automatically do conversion from a SQL Server DB to a SQLite DB by liron.

Here is the code project article.

Damith
  • 62,401
  • 13
  • 102
  • 153
1

I suggest to use transactions in SQLite, else the Indices are built with every new record added, not when one block of work is done.

As i don't know about the structure of your data, it is hard to give concrete advice, but writing a small application that can read from SQLServer and write to SQLite is often a practical start for migration. With the bonus of having a full wrapper after migration, which you can use in future.

Mare Infinitus
  • 8,024
  • 8
  • 64
  • 113
  • As @Damith mentioned the solution in this article: http://www.codeproject.com/Articles/26932/Convert-SQL-Server-DB-to-SQLite-DB – Saw Jun 09 '12 at 12:06