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.

  • Using T-SQL, how can I catch the OS error code?
  • Aggregating grouped data with repeating data
  • Viable T-SQL alternative to a nested IF ELSE for comparisons in which CASE fails
  • decimal value from db displayed in textbox
  • Replacing multiple attributes with transact-sql replace - XML
  • What framework to use to connect to a SQL Server instance from an iPhone?
  • 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?

  • Invalid object name 'dbo.stream'
  • full text search, filter results for best match
  • How to create a basic SQL Server job with SQL Server Express?
  • Table valued parameter in a stored procedure gets execute permissions denied error
  • Using data from query
  • Why doesn't SQL Server come preinstalled with .net Framework for CLR Integration?
  • 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.