Issues with SQL Server Import and Export Wizard

The SQL Server Import and Export Wizard has been around for a while. It’s a simple and very useful tool for importing or exporting data. You simply need to give the Wizard a data source, name a destination, and let the Wizard do the magic: importing or exporting data. But there can be instances where the SQL Server Import and Export Wizard fails to perform as designed and needs help to finish the basic importing or exporting of data.

     Problem

Moving data into SQL Server is something that most DBAs or Developers are faced with probably on a daily basis.  One simple way of doing this is by using the Import / Export wizard, but along with this option there are several other ways of loading data into SQL Server tables. Another common technique would be to use SSIS.  In this tip we take a look at some of these other options for importing data into SQL Server.

Solution

In addition to using the Import / Export wizards or SSIS to move data into SQL Server there are also a few other options for doing this that are built into SQL Server.  Some these other options include bcp, BULK INSERT, OPENROWSET as well as others.  The following examples show you some of these different options for importing data and how you can use some of these inline with your T-SQL code as well as others that can be run from the command line.

---

BCP

This is one of the options that is mostly widely used.  One reason for this is that it has been around for awhile, so DBAs have come quite familiar with this command.  This command allows you to both import and export data, but is primarily used for text data formats.  In addition, this command is generally run from a Windows command prompt, but could also be called from a stored procedure by using xp_cmdshell or called from a SSIS package.

Here is a simple command for importing data from file C:\ImportData.txt into table dbo.ImportTest.

bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

For more information about bcp

BULK INSERT

This command is a T-SQL command that allows you to import data directly from within SQL Server by using T-SQL.  This command imports data from file C:\ImportData.txt into table dbo.ImportTest.

BULK INSERT dbo.ImportTest
FROM 'C:\ImportData.txt'
WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

For more information about BULK INSERT click here.

---

OPENROWSET

This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server.  By using this command along with an INSERT INTO command we can load data from the specified data source into a SQL Server table.

This command will pull in all data from worksheet [Sheet1$]. By using the INSERT INTO command you can insert the query results into table dbo.ImportTest.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

Here is another example where data is pulled from worksheet [Sheet1$] by using a SELECT * FROM command. Again, by using the INSERT INTO command you can insert the query results into table dbo.ImportTest.   The query can be any valid SQL query, so you can filter the columns and rows by using this option.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]')

For more information about OPENROWSET click here.

---

OPENDATASOURCE

This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. This is similar to the OPENROWSET command.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

For more information about OPENDATASOURCE click here.

---

OPENQUERY

Another option is OPENQUERY.  This is another command that allows you to issue a T-SQL command to select data and again with the INSERT INTO option we can load data into our table.  There are two steps with this process, first a linked server is setup and then second the query is issued using the OPENQUERY command.  This option allow you to filter the columns and rows by the query that is issued against your linked data source.

EXEC sp_addlinkedserver 'ImportData',
   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
   'C:\ImportData.xls',
   NULL,
   'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT *
FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

For more information about OPENQUERY click here.

---

Linked Servers

Here is yet another option with setting up a linked server and then issuing a straight SQL statement against the linked server.  This again has two steps, first the linked server is setup and secondly a SQL command is issued against the linked data source.

EXEC sp_addlinkedserver 'ImportData',
   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
   'C:\ImportData.xls',
   NULL,
   'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT * FROM ImportData...Sheet1$

For more information about Linked Servers click here.

As you can see right out of the box SQL Server offers many ways of importing data into SQL Server.  Take a look at these different options to see what satisfies your database requirements.

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 :

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’ 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 :





               

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 :

             

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.




            

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.