none
Transfer data from SQL 2000 to SQL 2012 using SSIS

    Question

  • Hi,

    I want to transfer data from SQL 2000 to SQL 2012 using SSIS. Schema of both database is different.

    Is it possible to move data from SQL 2000 to SQL 2012.

    Thanks,

    Mickey

    Thursday, March 22, 2012 5:07 PM

Answers

  • So, i can create a SSIS package in SQL 2012 which will transfer data from SQL 2000 to my new database in SQL 2012.

    Let me add one more thing, is that SQL 2000 is working in 6.5 compatibilty mode. Will it work in this case.

    Can you please provide a link from where i can get a sample of data transfer in SSIS.

    Thanks,

    Mickey

    Mickey, yes, use the OLEDB provider that will take care of connecting to SQL Server 2000, the database compatibility mode is even less relevant because you only want to get the data out of it. Just connect to it, nothing else is needed.

    One note though, looks like you are migrating a database, in this case there are better approaches, e.g. back it up, restore to SQL 2005 then backup and restore to 2012, at this stage you are done!


    Arthur My Blog

    • Proposed as answer by Eileen Zhao Tuesday, March 27, 2012 6:31 AM
    • Marked as answer by Eileen Zhao Thursday, March 29, 2012 2:10 AM
    Friday, March 23, 2012 3:17 PM
    Moderator

All replies

  • Hi, yes it's possible and SSIS (or SSDT in SQL 2012) is the right tool for that (since you need to perform some mapping due to schema differences).

    David.

    Thursday, March 22, 2012 5:18 PM
  • The Data Export Wizard (right-click on the database --> tasks --> Data Export) from the SQL Server 's SSMS 2012 will do the trick. And use the query option to pick the data up. You can choose multiple sources. The entire work can be saved to disk as SSIS package that you can later open in SSDT.

    Arthur My Blog

    • Proposed as answer by Koen Verbeeck Friday, March 23, 2012 1:13 PM
    Thursday, March 22, 2012 5:30 PM
    Moderator
  • So, i can create a SSIS package in SQL 2012 which will transfer data from SQL 2000 to my new database in SQL 2012.

    Let me add one more thing, is that SQL 2000 is working in 6.5 compatibilty mode. Will it work in this case.

    Can you please provide a link from where i can get a sample of data transfer in SSIS.

    Thanks,

    Mickey

    Friday, March 23, 2012 8:49 AM
  • Hi, it should work for all versions of SQL Server.

    Check these links for some sample SSIS packages:

    sql-server-integration-services-ssis-data-flow

    SSIS Tutorial: Creating a Simple ETL Package

    David.

    • Proposed as answer by Koen Verbeeck Friday, March 23, 2012 1:13 PM
    Friday, March 23, 2012 11:26 AM
  • So, i can create a SSIS package in SQL 2012 which will transfer data from SQL 2000 to my new database in SQL 2012.

    Let me add one more thing, is that SQL 2000 is working in 6.5 compatibilty mode. Will it work in this case.

    Can you please provide a link from where i can get a sample of data transfer in SSIS.

    Thanks,

    Mickey

    Mickey, yes, use the OLEDB provider that will take care of connecting to SQL Server 2000, the database compatibility mode is even less relevant because you only want to get the data out of it. Just connect to it, nothing else is needed.

    One note though, looks like you are migrating a database, in this case there are better approaches, e.g. back it up, restore to SQL 2005 then backup and restore to 2012, at this stage you are done!


    Arthur My Blog

    • Proposed as answer by Eileen Zhao Tuesday, March 27, 2012 6:31 AM
    • Marked as answer by Eileen Zhao Thursday, March 29, 2012 2:10 AM
    Friday, March 23, 2012 3:17 PM
    Moderator