Common Issues with the SQL Server Import and Export Wizard

Category : Blogs Published : March 21, 2013 User Rating : 4.5 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




The SQL Server Import and Export Wizard is useful for copying data from one data source (e.g. a SQL Server database) to another. Although the interface is fairly simple there are a few “gotchas” to be aware of. Here are a few issues I’ve found while loading data from one SQL Server database into another.

Identity Columns

The wizard doesn’t treat identity columns any differently to other columns, so will usually fail when trying to insert data into a table that has an identity column. However the error message can be a bit misleading :
- Validating (Error)
Messages

• Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "SystemInformationID".
(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 - BuildVersion" (28)" 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)
The issue here is that the ‘SystemInformationID’ column in this table is defined as an identity column, although that’s not immediately obvious from the error message. Fortunately there is an easy solution in that you can select an option in the wizard to allow specific values to be inserted into the identity column, much like you can with a SQL query. To do this select the relevant tables in the ‘Select Source Tables and Views’ page by clicking the checkbox in the header for all tables, or you can just select which tables you want copying. Make sure the tables you want are actually selected (i.e. not just checked). In my case I’ve just selected all the tables in my database :
SQL Server Import Wizard 1.jpg

Click on the ‘Edit mappings…’ button towards the bottom of the screen and the following window should appear:
SQL Server Import Wizard 2.jpg

If you select the ‘Enable identity insert’ as indicated in the picture above then the values of any identity columns will simply be copied across from the source database.

Timestamp Columns

The wizard will also attempt to copy any timestamp columns in the same way it would for a column of any other data type. Unfortunately timestamp columns can’t be explicitly set to a specific value so this will always fail. If you try it then you will probably get an error message like this one (obviously the column name will be different for you) :
- Validating (Error)
Messages

• Error 0xc0202048: Data Flow Task 1: Attempting insertion into the row version column "LastUpdated". Cannot insert into a row version column.
(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: "Destination 4 - Customer" 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)
Again the error is perhaps a bit misleading as there is no mention of ‘timestamp’, but row version is just a synonym for timestamp. The solution is to not copy any columns that are timestamps. To do this you just need to click the ‘Edit Mappings…’ in the ‘Select Tables and Views’ screen for the table in question. This should display a screen similar to this one : SQL Server Import Wizard 3.jpg

As you can see in my table the ‘LastUpdated’ column is a timestamp column. To stop the error occurring just set the destination to ‘ignore’ in the drop down that appears when you click on that cell : SQL Server Import Wizard 4.jpg
If there is more than one table with a timestamp you’ll need to repeat this for each table.

Constraints

If the table has foreign key constraints on then the chances are you will get a constraint failure message at some point. The Wizard does not load tables in any specific order for constraints, so it is quite possible that the foreign key table will get loaded before the table it refers to is loaded, causing a foreign key constraint failure. The error message will be something like : “The INSERT statement conflicted with the FOREIGN KEY constraint". The error in the Wizard will probably be similar to :
- Copying to [SalesLT].[ProductDescription] (Error)
Messages

• Information 0x402090e0: Data Flow Task 2: The final commit for the data insertion in "component "Destination 7 - ProductCategory" (207)" has ended.
(SQL Server Import and Export Wizard)

• Information 0x402090e0: Data Flow Task 2: The final commit for the data insertion in "component "Destination 5 - CustomerAddress" (31)" has ended.
(SQL Server Import and Export Wizard)

• Information 0x402090df: Data Flow Task 2: The final commit for the data insertion in "component "Destination 8 - ProductDescription" (262)" has started.
(SQL Server Import and Export Wizard)

• Information 0x402090e0: Data Flow Task 2: The final commit for the data insertion in "component "Destination 8 - ProductDescription" (262)" has ended.
(SQL Server Import and Export Wizard)

• Error 0xc0202009: Data Flow Task 2: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Product_ProductModel_ProductModelID". The conflict occurred in database "AdventureWorksLT2008R2Copy", table "SalesLT.ProductModel", column 'ProductModelID'.".
(SQL Server Import and Export Wizard)

• Error 0xc0209029: Data Flow Task 2: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (138)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (138)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

• Error 0xc0047022: Data Flow Task 2: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 6 - Product" (125) failed with error code 0xC0209029 while processing input "Destination Input" (138). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
The easiest way to get around this is to disable constraints during the load and then re-enable them again afterwards.

To disable all constraints on a specific table run the following SQL (in this case for the Address table) :
ALTER TABLE Address NOCHECK CONSTRAINT ALL
However you’ll need to disable constraints on all tables to be sure of avoiding errors. You can do this with the undocumented stored procedure sp_MSforeachtable as follows :
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
This will disable all constraints on all tables. Once the data has been loaded constraints can be re-enabled with the following :
EXEC sp_MSforeachtable @command1="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"
The ‘WITH CHECK’ option specifies that the data is validated against the constraint.

Triggers

If you have triggers in your database then these will not fire when using the import wizard. If you were relying on the trigger code running then you will need to do this manually once the data is imported.


Added March 2016 :

202 and 200 Data Conversion Errors

When importing or exporting data using a query, the VARCHAR data type is incorrectly recognised as a '200' data type and the NVARCHAR as a '202' data type. This causes the wizard to fail, with a message similar to that below in the 'Review Data Type Mapping' screen.
SSIS 200 and 202 error.jpg
This is a bug in some versions of SSIS and it only occurs when the data source is a query (rather than a table or view). The workaround I use is to insert the data from the query into a table, and then use the table as the data source. This avoids the need to use a query for the data source. An alternative is to create a view that references the query and use that as the data source. In both cases the table or view can be deleted once the data has been imported.
Link back to this article : http://www.sqlmatters.com/Articles/Common Issues with the SQL Server Import and Export Wizard.aspx

Keywords

SQL,scripts,SSIS,Import Export Wizard,constraints,identity


Comments
Post by T on Sun 06 Jul 2014 11:26. Report Inappropriate Post

Yhanks - This avoided a lot of frustration for me!
Post by luis on Thu 11 Dec 2014 18:12. Report Inappropriate Post

excelent article this resumes all issues that i had during the migration and i was looking in several post in the web but here summirizes perfectly
Post by Mohammed El-Said on Sat 20 Dec 2014 11:34. Report Inappropriate Post

Thanks a lot for useful information
Post by ram on Thu 22 Jan 2015 05:49. Report Inappropriate Post

thanks a lot
Post by sam on Tue 24 Feb 2015 13:33. Report Inappropriate Post

THANK YOU VERY MUCH..your solution worked for me... I was really frustrated, and had to get the server setup quickly..
Post by Alexandr on Wed 18 Mar 2015 13:12. Report Inappropriate Post

THANK YOU VERY MUCH!
Post by Nick Webb on Tue 07 Apr 2015 23:53. Report Inappropriate Post
Website : http://www.redwireservices.com
Thanks for this, it filled the hole in on all of these. One note, views can also be a problem, at least with SQL Server 2012 Web. Using import/export views are created as tables with data on the destination DB. Best to just script the database creation and excluded them from the import.
Post by Padma on Thu 09 Apr 2015 11:24. Report Inappropriate Post

Thanks for an excellent article
Post by Michael Carroll on Sun 12 Apr 2015 07:26. Report Inappropriate Post

Saved my bacon! Thanks a mill!
Post by ttt on Tue 28 Apr 2015 17:16. Report Inappropriate Post

Thank you so much! Migrating from mysql and got this errors...
Post by Praveen Kumar kothuri on Wed 03 Jun 2015 15:01. Report Inappropriate Post

Hi Team,

I am following the above steps(import \ export from source to destination) and i am able to export the data completely in destination database, however in my destination server keys(like primary,foreign,unique constraints etc) are missing for all tables.

Why the keys and constraints are missing? and how can i resolve this ?
Post by Umut Guncan on Mon 07 Sep 2015 13:08. Report Inappropriate Post

Perfect :)
But when i try to re-enable constraints, i am getting below error:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.CrmCompany_dbo.AspNetUsers_CrUser". The conflict occurred in database "KingFest4", table "dbo.AspNetUsers", column 'Id'.
Post by Hany on Tue 08 Sep 2015 08:16. Report Inappropriate Post

Thank you!
Post by DevCod on Wed 14 Oct 2015 05:24. Report Inappropriate Post

This was very useful. I used mass Identity Columns transfer settings and NO Check settings.
Post by Nick Webb on Fri 16 Oct 2015 23:04. Report Inappropriate Post
Website : http://www.redwireservices.com
I'd like to point readers at the SQL Azure Migration Wizard for this kind of work. Don't let the name fool you, we've used this to move databases from on-site to AWS and back without all the manual steps this article discusses. It really is the answer, especially when using SQL Server Web Edition for web sites.


https://sqlazuremw.codeplex.com/
Post by Ali on Wed 02 Dec 2015 14:25. Report Inappropriate Post

Thank you bro,
You saved my life :)
Post by Shannon on Fri 15 Jan 2016 19:28. Report Inappropriate Post

I tried some of the things mentioned, but didn't get far. But instead of pushing the data to excel I tried having excel pull the data from the sql server and it worked. The steps I took are: From excel (professional 2010) - data tab / from other sources / microsoft query / new data source / plug in your sql server name / pick the main table you need / OK / choose at least one column / next / next / check - view data or edit query in microsoft query / select view ->sql / paste your sql query in, close the editor / click OK, and let it go. It was able to pull from the server what i couldn't get SQL to either copy/paste into excel, or able to export it to excel as I was getting those error messages in this article. Hope it helps.
Post by Paul on Thu 28 Jan 2016 18:54. Report Inappropriate Post

I've been a dba for 30 years. This is an awesome post! I wish Microsoft showed details and snapshots like this.
Post by Jyothiish S R on Fri 04 Mar 2016 06:58. Report Inappropriate Post

Thank you...it really helped a lot.
Post by Mahesh on Mon 21 Mar 2016 19:00. Report Inappropriate Post

while exporting local table to remote server table in middle if connection drops, say it exported some 1000 records and if I try to export same table will export starts from beginning of the row table or will it stat from 1001th row? How can I export renaming records if connection drops?
Post by Mark on Wed 27 Apr 2016 21:18. Report Inappropriate Post
Website : http://www.sourceresearch.com
Thanks for your article. We had many of these sames issues and this fixed them all except the dts.dll file Windows 7 can't find. We used these techniques on a Windows 7 Pro computer and all is well
Post by Jerwin on Wed 11 May 2016 02:27. Report Inappropriate Post

Hi, I've tried all the scripts but still i'm getting error when importing my access database.. please advice..

Post by marwa on Thu 25 Aug 2016 12:44. Report Inappropriate Post

The easiest way to get around this is to disable constraints during the load and then re-enable them again afterwards.

To disable all constraints on a specific table run the following SQL (in this case for the Address table) :
ALTER TABLE Address NOCHECK CONSTRAINT ALL
However you’ll need to disable constraints on all tables to be sure of avoiding errors. You can do this with the undocumented stored procedure sp_MSforeachtable as follows :
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
This will disable all constraints on all tables. Once the data has been loaded constraints can be re-enabled with the following :
EXEC sp_MSforeachtable @command1="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"
The ‘WITH CHECK’ option specifies that the data is validated against the constraint.
************************************************************
I don't know where can i write this statements to Nocheck constraints or to undo the constraints ?
Help me please ,
Post by test on Mon 03 Oct 2016 14:47. Report Inappropriate Post

very helpful ! Can you also tell how we can control the no of rows to be exported
Post by Satya on Wed 12 Oct 2016 12:26. Report Inappropriate Post

HI,
I have tried to copy the data using Import/Export wizard, but getting below error. Please help.

• Error 0xc0202009: Source 1926 - Table [114]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E07.
(SQL Server Import and Export Wizard)
• Error 0xc02020e8: Source 1926 - Table [114]: Opening a rowset for "[Schema].[Table]" failed. Check that the object exists in the database.
(SQL Server Import and Export Wizard)
• Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)


I don't see table with this specific schema in source DB.
Post by satya on Wed 12 Oct 2016 12:57. Report Inappropriate Post

Sorry, i could see that this schema exists in views, but getting this error
Post by Ruffo on Tue 27 Dec 2016 22:26. Report Inappropriate Post

Thank you so much !!!! happy new year 2017 !!!!
Post by cann0nball on Wed 04 Jan 2017 16:31. Report Inappropriate Post

I recently used Import/Export wizard to copy the data from one huge (almost 14 billion rows) table to another.
The destination table has a couple of different datatypes, datetime2 instead of datetime and tinyint instead of int.
Truncations were ignored.

Operation completed successfuly, however wizard reported to only transfer about 7% of the rows.
However, If I do a count, I get the same number of rows for both tables, any ideas why?
Post by Rahul on Sat 07 Jan 2017 09:12. Report Inappropriate Post

Excellent...thank you so much
Post by Paul on Wed 11 Jan 2017 20:49. Report Inappropriate Post

A note of caution here, if you disable the FK constraints you may find that you cannot enable them again. This is because the exact thing that the FK constraint was configured to not allow has happened - you have data in the table with the FK that doesn't match up with the other table that it is referencing. Thus, you will have to clean up the imported data in the table prior to enabling the FK constraints. Of course, if you cleaned up the imported data prior to doing your import, you would NOT have to disable the FK constraints to begin with.
Post by SQL Matters on Fri 20 Jan 2017 10:49. Report Inappropriate Post

Hi Paul, thanks for your comments. However even if all your data matches up you may well have to disable constraints temporarily while doing the import. This is because the wizard doesn't look at constraints when determining the order to load tables, so a child record may get loaded before the parent record, causing a temporary constraint failure until the parent record is loaded. Once the import is complete all records should match up again (assuming that they did in the source data). To get around that issue I was recommending temporarily disabling constraints while doing the load, if that issue was encountered.
Post by Nathan on Thu 09 Feb 2017 16:26. Report Inappropriate Post

hi! thanks for the great tutorial! I would like to share the tool I use in my work https://www.devart.com/ssis/ there is a huge variety of sources that really save time! very fast and easy to use!

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered