Is A Orm The Right Tool To Use For Migrating Data?

Background We are in the process of upgrading a legacy import tool, what it does is it moves data from one database attached to SQL Server to a 2nd database on the same server with

Solution 1:

SSIS is the perfect tool to do this, hands down.

An ORM is only for CRUD operations(as you mentioned correctly) in applications, and has a high probability of raising serious concerns for large data transfers. Most ORMs are not even suggested for CRUD operations involving large number of rows, forget database level data migration. ORMs are mostly used for ease-of-coding for persistent data connections between a software application and a database.

SSIS on the other hand is made for ETL (Extract Transform Load), at database, datawarehouse levels, safely. The migration occurs at a significantly higher rate as well, as compared to stored procedures.

Another important thing I wanted to add, is the fact that SSIS is super easy(from my experience). Most operations involve drag and drop of ETL controls on the Visual Studio designer and then configuring the data types on the configuration screens. Unless you really love to write code, or in extremely complex scenarios, you would be well off with just that and a few data type Transformation(T) snippets.

I understand, the boss thinks of it as an unnecessary investment currently. However, SSIS is Microsoft's trump card in the data warehousing scene. Looking at your current requirements, it is exactly what your organization needs. By the experience of it in our organization, its an investment that will prove worth every penny as long as Microsoft lives.

Solution 2:

I also wouldn't use a full blown ORM, but a micro ORM like Dapper is a great for tasks like this(among other things). Super fast and you run it all pretty close to the metal for high performance and ease of use if you are familiar with TSQL and c# it is a snap to use. ( you can be productive in 15 minutes)

Just finished a similar project, using it to move data from server to server and it work and performed like a champ.

https://code.google.com/p/dapper-dot-net/

Solution 3:

An ORM is definitely not the right tool, as you rightly point out they are for OLTP applications.

Given that SSIS is off the table (it is the right choice were it an option), I'd consider looking at Rhino ETL. Very flexible, and you can use SqlBulkCopy with it which obviously you should consider doing here. It's open source, too boot.