Transfer/Transform Data From SQL Server 2008 DB to Another

Question

I have rewritten an old program and designed a new database for it. I need to transfer the old data to the new database. The new database has a new schema, so I will have to manipulate much of the data that is going to be transfered.

I have thought about writing a console app with 2 linq to sql contexts, one for each database and coding the transformation logic and the inserts.

I have also thought about using SSIS, but I have never used it before.

Is SSIS suited for this sort of thing? Where can I learn how to do this in SSIS?

Answer 1

It can and is suited for this but if you haven't used it the learning curve is a little steep and setup / config can be a bit obtuse.

If you are familar with Linq then I would go that route before trying to learn SSIS especially if this is a one time thing and time is a factor.

There is also the import and export wizard in SQL Server (uses SSIS) and you can setup straight copies from one table to another and / or custom sql statements. This should work for fairly simple conversions. For transformations that require more advanced logic it won't work.

Answer 2

I would go with the SSIS. It is an ETL tool (which is what you are doing). Plus, you learn something new.

Here is some great SSIS content

http://stackoverflow.com/questions/142015/ssis-gurus-links-to-great-learning-content-requested