I am trying to export data from my local server to the hosting server. However I get errors when executing it: Validating (Error) Messages Error 0xc0202049: Data Flow Task: Failure inserting into the read-only column "ID".(SQL Server Import and Export Wizard) Error 0xc0202045: Data Flow Task: Column metadata validation failed.(SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task: "component "Destination 7 - Batches" (497)" failed validation and returned validation status "VS_ISBROKEN".(SQL Server Import and Export Wizard) Error 0xc004700c: Data Flow Task: One or more component failed validation.(SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task: There were errors during task validation.(SQL Server Import and Export Wizard) Any idea?

Did you try to insert values in the ID field and this is an identity? This will not work...

I am just trying to copy all data from one server to another. It used to be simple task in SQl 2000. I am not sure how to do this now...

...I'm qute sure that you have the same issue with 2000, too! You simply can't do an insert into an identity column (by default). However you might try to switch "Enable identity insert" in the "Mappings" screen on...

I used to be able to Run Wizard in 2000 and transfer all the data to the server. It si the same database.. I had an option to recreate destination tables first... What is the simplest way to do it in new 2005? I can't figure it out. Basicly I want my development SQL Database have in synch with production hosting SQl Server... It is not some rocket science... I should be able to run some kind of wizard... The one I run does not allow me to slect drop destination tables, this option is greyd out... Also it is on table level, not for all tables. If I design SSIS package I would have to modify it everytime I add new tables to the laptop DB, so the Wizard slution like in SQL 2000 seems much better. Since the wizard in 2000 worked for me I am considering to downgrade to SQL 2000, it is a shame I have to do it, because 2005 suposed to be better product, yet it does not meet my expectations in this matter. I wonder how other users find SQL 2005 comparing SQL 2000 in practical use, not just in theory.

I'm very sorry, I can't understand your problem. From my point of view you can exactly do what you did with SQL 2000 with SQL 2005, too. Perhaps you have to explain it more detailed (step by step) what you did with 2000 and what you now do with 2005. I can drop and recreate tables without any problem. If you have identities, you had to (and still have to) click on "enable identity inserts"... Or did you probably use the "copy database objects" wizzard? But I don't think so, that didn't fit to your description...

For some reason the option to recreate tables is greyed out in SQL 2005 wizard. I do not know why. Also this option is available on the table level. You have to click each table and change it. It used to be global setting for all tables in SQL 2000...

RADIOGENERIS wrote:You have to click each table and change it. It used to be global setting for all tables in SQL 2000... Surely the ability to define different behaviour for each one of them is a good thing?

Sure, but having an easy way to change the default behaviour would be something great, too?! Perhaps something to suggest "officially"?

It was done this way in SQl 2000, why to remove this global option??? Also why is this option greyed out? Nobody can answer this question. What can be the reason? Thanks,

This option isdesigned to be used when the destination table is newly created. It would allow executing a saved package after the wizard creates the table, without modifications. In your case (the table already exists) you would probably want to use the "Delete rows indestination table" option. We did not want to allow deleting tables that are not created by the wizard, since that may cause some unwished effects. I agree that inability to set these optionsfor more than one transfer at the time was a serious usability problem, and it has been addressed in SP1. The SP1 will allow bulk edits on all selected transfers and in addition to setting these options itwould allow setting destination schemas to more than one destination table. Thanks.

Bob, Thank you for your explanation. I am so happy you guys have noticed that. I am waiting fo SP1, would you know when this will become available? My suggestion is that the option to recreate table should be always available, it was done this way in SQL 2000 and it worked. Let the user decide what they want. They do not have to check this option... You could give them some warning once they check it. I guess deleting rows in a destination table would work for me for now. My point is that SQL Server 2000 data export/import wizard was a very good tool, I am sure many people would agree with me. Why to remove some of it's great functionality??? It was simple and sufficient and if you need more you would design a package. It creted views, stored procedures for you, all in one step. It was perfect for me, as I need to synchronize my laptop with hosting environment on regular basis. I develop new stored procedures, tables etc. at the end of day I would just run a wizard... Now the process is very time consuming. Hopefully SP1 will adress those issues. Seems like SQL 2005 is a very good product, yet it needs few tweaks. Not everybody is SSIS expert :) Also have you tried to open this package generated by Wizard in Visual Studio? It takes forever. I have 1.86 Ghz laptop with 1GB memory... I think you guys are so ahead in technology that you may forget about simple use cases sometimes :) Rafal

You have a good point Rafal. There's some very simple functionality in Enterprise Manager around scripting out objects that i am really missing. If you want something to go into SP1 or the next version you should raise it at the Feedback Center: http://lab.msdn.microsoft.com/productfeedback/default.aspx -Jamie

Seems like I am not the first one who noticed those issues. I have found few suggestions submited already. Please take a look: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=9eb6c773-2dbb-4a27-b9d8-225d6ed4385a

I am not quite sure when SP1 will get out, my guess is sometime in the spring time. You are welcome to post the feedback on the ways you wish the wizard to get improved. That will most certainly influence the work we choose to do on it. We do sometimes forget about simple use cases, but it is more often that we have to prioritize things we do to better utilize our resources. These priority lists are based on the feedback we hear from you guys. So again, do not hesitate to send it. There is also one thing I wanted to point out about comparing DTS and SSIS import/export wizard. While they look similar, the used underlying components are a way different, and thathad influence on some of the UI decisions we made. Thanks.

RADIOGENERIS wrote: I am trying to export data from my local server to the hosting server. However I get errors when executing it: Validating (Error) Messages Error 0xc0202049: Data Flow Task: Failure inserting into the read-only column "ID".(SQL Server Import and Export Wizard) Error 0xc0202045: Data Flow Task: Column metadata validation failed.(SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task: "component "Destination 7 - Batches" (497)" failed validation and returned validation status "VS_ISBROKEN".(SQL Server Import and Export Wizard) Error 0xc004700c: Data Flow Task: One or more component failed validation.(SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task: There were errors during task validation.(SQL Server Import and Export Wizard) Any idea? I'm getting the exact same error as you. And I've made sure of the following: Destination column is NOT read-only Enable Identity Insert is NOT checked Took Bob's advice to use "Delete rows in destination table" My login has the "db_owner" database role I'm still getting this error. In addition to this error, my source is an Excel spreadsheet, and I need to import multiple tabs to the same table using unique MS_IDENT values (hence the need for "Append rows in destination table"). This used to be allowed in SQL Server 2000, not sure why it's no longer available.

Could you check if "Optimize for many tables" is checked on the "Select Tables" page. There is a known issue that prevents using identity insert settings if this option is selected. The workaround is to uncheck the "Optimize for many tables" checkbox. BTW, how many tables do you transfer in a batch? Thanks, Bob

Hey simply check the option 'Optimize for many tables' and it would work perfectly allowing to Export all the data wether it has to be inserted in Identity Columns. I have been working on Sql 2005 platform and during Transfer of data to the production server i use the same technique as u do. Once I too faced this problem of insertion in Identity Columns. But Solved it myself allowing 'Optimize for many tables'. Hope this helps, Regards, SRK

Hey when your trying to import data from one table to another using SQL 2000 Import/Export wizard, it copies the data and transfers it. It doesn't check to prevent duplication of identity column data. This happens in SQL 2000. But in sql server 2005 you can't simply insert or transfer the Identity column data of a table. To do that you have to follow while in the Import wizard in SQL 2005, select the source table and its destination table. see the mapping you have EDIT option, click edit, you will see column mapping wizard, look out for "enable identity insert check box", click on it , there you are. But make sure you won't have duplicated data in Identity column. Hope this helps

SQL Server 2000 was a great product. 2005 and 2008 have been designed without any care for developers. The security culture has made the whole product a garbage. I'm about to find a company that cares about the time developers have spent on their products and always design software that is backward compatible. Sorry back the SSIS Designer, packages ets. are by far warse than those in 2000.

Why? We are responsible for our data not you. We want to make anything with our data. We do not want you to decide for us. The most unwished effect is that I can't do my job.

Hi, I got the same error. Solution: I disabled the identity key in the destination table and it worked for me. Thanks a lot.