Importing Tables with Identity properties (uisng Import wizard)

I am using the Import wizard to import a SQL2000 database to SQL2005 and noticed 2 problems:

1. all tables and views were selected; the tables were imported correctly but the views were created as tables, ignoring the "Create view" syntax. The SQL generated contains "Create table" syntax instead of "Create View".

2. when a table contained a column with an "identity" property, the data was successfully imported, but the values for the "identity" column were not preserved, instead they were resquenced from 1 with an increment of 1 (the default values for an identity). When I opened the "Edit" (under "Mapping"), "enable identity insert" was not checked.

A further note: I created all tables in the SQL2005 database before running the Import.

1 - How would you represent a selection of tables, calculations and some view DDL in a text file? SSIS is about data movement, not object migration. This is what happens and is entirely correct. You often use views to expose a limited set of data, perhaps including calculations or such like, and you would then use SSIS to take that data and move it to a new system.

2 The wizard does not expose all options otherwise if would not be a very good wizard, and this is just one of those assumptions. It is in part no doubt based on the difficult of determining an identity column through the OLE-DB provider specifications, and it is the same old issue you had in DTS.

If you want to migrate SQL 2000 to SQL 2005 I suggest you have a look in Books Online it covers several options. A backup and restore is perhaps the easiest method I have found to take a SQL 2000 DB to a new SQL 2005 server.

|||

Darren:

Your reply was typical of someone who believes that Microsoft is incapable of making an error, in this case a grevious error since it MODIFIES data defined as an IDENTITY, instead of COPYING it. The SSIS wizard clearly needs work; it is unacceptable in its present form. (If you need an example of how a data migration wizard SHOULD work, review the one used by SQL SERVER 7 to migrate data from SQL SERVER 6.5).

I have manged to MOVE all of the data from SQL2000 to SQL2005 using a 3-step process: 1) run a SQL script that creates the table structures complete with all constraints. (This is ANOTHER area where the wizard is incomplete; it creates ONLY the table structure, minus all constraints). 2). Use the wizard to copy all tables that DO NOT CONTAIN identity columns. 3). Use the wizard to copy ONLY tables that DO CONTAIN identity columns, but EDIT each table entry checking ON "ENABLE IDENTITY INSERT".Using the 3-step process, I was able to bring ALL of the data over in one session, not following your suggestion of bringing the data across in a piece-meal process, never knowing if I had copied ALL of the data).

So, the task CAN BE DONE, if one is able to understand the failings of the wizard, and work around them appropriately.

|||

Thanks for adding your experiences.

I don't see that I suggested a piece-meal approach, I just stated there is an issue, and whilst not necessary to the answer I do think is big problem, I have hit it myself, and even logged a PPS call for the same issue in DTS. Have you feedback at all?

I recall the migration wizard and yes it was ideal for the job, but personally I am happy to accept the limitations covered with SSIS, because SSIS is really an ETL tool, not a migration tool. A tool designed specifically for SQL to SQL migrations would not be an effective ETL tool. Just because the wizard can be used does not mean it is ideal or even the best tool. There are much better tools for generating scripts and transferring structures for example, but the SSIS limitation of not creating constraints sounds like a bonus. Having constraints in place when loading data increases the complexity as you need to load related tables in order, and tables with circular references just cannot be loaded. Performance will also be much faster will with them as well. I would apply constraints after the load. That would all be me defending MS of course, but I try and think what the role for the product is too.

A non-piecemeal method, in just 1 step, which I did suggest was a backup and restore. It suffers none of the issues you covered, and is in many ways faster and more reliable than anything else. It may of course introduce additional implications, but then we have choices.