Transfer/Transform Data From SQL Server 2008 DB to Another

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.

  • How to control SSIS package flow based on record count returned by a query?
  • sql server statement returning values 2 times
  • Sending HTTP POST request from SQL Server 2012 or SQL CLR C#
  • SQL - How to backup a database and export as a MDF file with MS SQL 2008 R2
  • CROSS APPLY with table valued function restriction performance
  • Using a variable for table name in 'From' clause in SQL Server 2008
  • 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?

  • What is the most cost-effective way to break up a centralised database?
  • Execution Time Slower with each Iteration of the same SPROC
  • Implementing transactions over multiple databases
  • Recognize application connected to SQL Server 2005
  • How to send out email at a user's local time in .NET / Sql Server?
  • Horrible performance when using SqlCommand Async methods
  • 2 Solutions collect form web for “Transfer/Transform Data From SQL Server 2008 DB to Another”

    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.

    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

    What are the recommended learning material for SSIS?

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.