Why Does Save Wizard Have to Upload to Server
Ane Click Feedback
Please assist us to amend the site past 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 information source (eastward.g. a SQL Server database) to another. Although the interface is adequately simple there are a few "gotchas" to be enlightened of. Here are a few issues I've establish while loading information from one SQL Server database into another.
Identity Columns
The wizard doesn't treat identity columns any differently to other columns, then will usually fail when trying to insert data into a table that has an identity column. All the same the error message tin be a scrap misleading :
- Validating (Error)
Letters
• Fault 0xc0202049: Data Flow Task i: Failure inserting into the read-just cavalcade "SystemInformationID".
(SQL Server Import and Consign Sorcerer)
• Fault 0xc0202045: Information Menstruum Job i: Column metadata validation failed.
(SQL Server Import and Export Wizard)
• Error 0xc004706b: Data Menstruation Task 1: "component "Destination - BuildVersion" (28)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Consign Wizard)
• Fault 0xc004700c: Data Flow Task 1: Ane or more component failed validation.
(SQL Server Import and Export Magician)
The outcome here is that the 'SystemInformationID' column in this table is divers as an identity column, although that's not immediately obvious from the error message. Fortunately there is an easy solution in that you lot can select an option in the wizard to allow specific values to be inserted into the identity cavalcade, much like you can with a SQL query. To practise this select the relevant tables in the 'Select Source Tables and Views' folio past clicking the checkbox in the header for all tables, or y'all can just select which tables you lot want copying. Make certain the tables you want are actually selected (i.e. not but checked). In my example I've just selected all the tables in my database :
Click on the 'Edit mappings…' button towards the bottom of the screen and the following window should appear:
If you select the 'Enable identity insert' every bit indicated in the picture in a higher place then the values of any identity columns will merely exist copied across from the source database.
Timestamp Columns
The wizard volition likewise attempt to re-create whatever timestamp columns in the same way it would for a cavalcade of whatever other data type. Unfortunately timestamp columns can't exist explicitly set to a specific value and so this will always neglect. If you try it so you will probably get an fault message like this i (plainly the column proper noun will be unlike for y'all) :
- Validating (Error)
Messages
• Mistake 0xc0202048: Data Flow Task 1: Attempting insertion into the row version cavalcade "LastUpdated". Cannot insert into a row version column.
(SQL Server Import and Export Wizard)
• Fault 0xc0202045: Data Flow Task i: Column metadata validation failed.
(SQL Server Import and Export Wizard)
• Error 0xc004706b: Data Flow Task 1: "Destination 4 - Client" failed validation and returned validation condition "VS_ISBROKEN".
(SQL Server Import and Export Wizard)
• Fault 0xc004700c: Data Flow Task 1: One or more component failed validation.
(SQL Server Import and Consign Sorcerer)
Error 0xc0024107: Information Catamenia Task 1: There were errors during task validation.
(SQL Server Import and Export Wizard)
Again the fault 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 non copy any columns that are timestamps. To do this you lot merely 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 : 
As yous tin can see in my tabular array the 'LastUpdated' column is a timestamp column. To end the error occurring but set the destination to 'ignore' in the drop down that appears when y'all click on that cell : 
If there is more than one table with a timestamp you'll need to repeat this for each tabular array.
Constraints
If the table has strange key constraints on and so the chances are you lot will become a constraint failure message at some point. The Wizard does not load tables in whatsoever specific lodge for constraints, so it is quite possible that the foreign key tabular array will go loaded before the tabular array it refers to is loaded, causing a foreign fundamental constraint failure. The error message will exist something similar : "The INSERT statement conflicted with the Strange KEY constraint". The error in the Wizard will probably exist similar to :
- Copying to [SalesLT].[ProductDescription] (Mistake)
Messages
• Data 0x402090e0: Information Flow Task 2: The concluding commit for the data insertion in "component "Destination 7 - ProductCategory" (207)" has concluded.
(SQL Server Import and Export Magician)
• Information 0x402090e0: Information Period Task 2: The concluding commit for the information insertion in "component "Destination 5 - CustomerAddress" (31)" has ended.
(SQL Server Import and Export Wizard)
• Information 0x402090df: Data Flow Job 2: The terminal commit for the data insertion in "component "Destination eight - ProductDescription" (262)" has started.
(SQL Server Import and Export Wizard)
• Data 0x402090e0: Data Menstruum Task 2: The final commit for the data insertion in "component "Destination 8 - ProductDescription" (262)" has concluded.
(SQL Server Import and Consign Wizard)
• Error 0xc0202009: Data Flow Chore two: SSIS Fault Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Fault code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Customer 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 Clarification: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Product_ProductModel_ProductModelID". The conflict occurred in database "AdventureWorksLT2008R2Copy", tabular array "SalesLT.ProductModel", cavalcade 'ProductModelID'.".
(SQL Server Import and Export Wizard)
• Error 0xc0209029: Data Menstruum Task 2: SSIS Fault 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. In that location may exist error messages posted earlier this with more information most the failure.
(SQL Server Import and Export Wizard)
• Error 0xc0047022: Data Flow Job ii: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination vi - Product" (125) failed with error code 0xC0209029 while processing input "Destination Input" (138). The identified component returned an fault from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Information Flow task to stop running. There may be error letters posted earlier this with more information almost the failure.
(SQL Server Import and Export Sorcerer)
The easiest way to get around this is to disable constraints during the load and so re-enable them again afterwards.
To disable all constraints on a specific table run the following SQL (in this case for the Accost table) :
Modify TABLE Address NOCHECK CONSTRAINT ALL
Withal you'll need to disable constraints on all tables to be certain of avoiding errors. You lot can exercise this with the undocumented stored process sp_MSforeachtable as follows :
EXEC sp_MSforeachtable @command1="Change Tabular array ? NOCHECK CONSTRAINT ALL"
This will disable all constraints on all tables. In one case the data has been loaded constraints can be re-enabled with the following :
EXEC sp_MSforeachtable @command1="Alter TABLE ? WITH Cheque Check CONSTRAINT ALL"
The 'WITH Cheque' option specifies that the data is validated against the constraint.
Triggers
If yous accept triggers in your database then these will not fire when using the import sorcerer. If yous were relying on the trigger code running then you will need to exercise 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 information type is incorrectly recognised as a '200' information blazon and the NVARCHAR as a '202' data type. This causes the wizard to fail, with a message similar to that beneath in the 'Review Data Type Mapping' screen.
This is a issues in some versions of SSIS and it just occurs when the data source is a query (rather than a table or view). The workaround I use is to insert the information from the query into a tabular array, and then apply the table every bit the information source. This avoids the need to use a query for the information source. An alternative is to create a view that references the query and employ that as the data source. In both cases the table or view can be deleted once the data has been imported.
octomancleaskulty.blogspot.com
Source: https://www.sqlmatters.com/Articles/Common%20Issues%20with%20the%20SQL%20Server%20Import%20and%20Export%20Wizard.aspx