importing data using import export wizard in ssms
Hello I am trying to import around 400 tables from another sql server instance and both the Databases have the same schema. I want to delete the existing data in the destination and fill new but when i edit to delete the tables on the destination it shows error that it cant truncate the table as its related to another table. The error message which i got was ERROR: Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "ADJUNCTIVEID". (SQL Server Import and Export Wizard) Error 0xc0202045: Data Flow Task 1: Column metadata validation failed. (SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task 1: "component "Destination 3 - ADJUNCTIVEINCOMEELIGIBILITY" (252)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard) Error 0xc004700c: Data Flow Task 1: One or more component failed validation. (SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task 1: There were errors during task validation. (SQL Server Import and Export Wizard) So how shd i do this import of 400 tables. Suggest me the way you guys think is better Thanks dimrd_SQL
December 21st, 2010 1:34pm

If this is a straight copy of the database, consider doing a backup and restore of the original, or creating a snapshot, or setting up replication. What is happening is that you have IDENTITY columns in some of the tables and the wizard does not know what to do about that. Also, if you have foreign keys in your database, then you need to be careful of the order in which the tables are truncated and reloaded. Basically, truncate the child table(s) first, then the parent(s), then reload the parent(s), then the child(ren). Have you gone through the wizard and instead of running it, saved it to an SSIS dtsx pacakge then openned it in BIDS? With 400 tables, it won't be pretty, so maybe choose only a dozen or so for this one time and then see what the wizard has created for you. Reverse engineer it; take it apart. Look at the Destination connections in the Data Flows. Do they specify Keep Identities?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
December 21st, 2010 2:50pm

i am a developer i do not have right to do backup and restore so what i want is to delete the data at the destination rows and put the new one only so is it possible with the backup restore If yes then what wording shd i put in the request to the dba Thanks dimrd_SQL
December 21st, 2010 3:09pm

Ask your DBA if he can develop a simple T-SQL script to do the backup and restore. Something like: BACKUP DATABASE [MyDatabase] TO DISK = N'\\MyServer\MyShare\db.bak' WITH COPY_ONLY, NOFORMAT , NOINIT , NAME = N'My Database -Full Database Backup', SKIP , NOREWIND , NOUNLOAD , STATS = 10 GO Then to restore on the other server: RESTORE DATABASE [MyDatabase] FROM DISK = N'\\MyServer\MyShare\db.bak' WITH FILE = 1, MOVE N'Db_Data' TO N'D:\MSSQL\Db.MDF', MOVE N'Db_Log' TO N'D:\LOGS\Db.LDF', NOUNLOAD , REPLACE, STATS = 10 GO Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
December 21st, 2010 3:22pm

so acc to you we can not do the thing we want with the simple backup and restore like the way i said with the SSMS backup. Thanksdimrd_SQL
December 21st, 2010 3:37pm

so acc to you we can not do the thing we want with the simple backup and restore like the way i said with the SSMS backup. What? I never said you couldn't do a Backup/Restore with SSMS. I assumed (possibly in error) that this is an operation that you would want to have happen on a regular basis and therefore offered a script as the solution. Personally, if it has to be repeated and can be done via a script instead of the GUI, I'll go for a script. "If you have a difficult job, give it to a lazy man, he'll find an easy way to do it!"Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
December 21st, 2010 3:43pm

todd I did not want to hurt you Sorry if i hurt I really appreciate your help I was just asking just words might be wrong It helped me. Thanksdimrd_SQL
December 21st, 2010 3:50pm

No offense taken. And you're welcome. Glad to help out.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
December 21st, 2010 3:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics