SQL SERVER – Easy Way to Import and Export SQL Server Data

I know from my personal experience that many SQL Server novice users have issues while uploading and downloading data in various formats. In my practice, this kind of tasks arise very often. I would prefer to automate the process for the recurrent tasks, if the file structure is not going to be changed. I can create a SSIS package, deploy it on a server and then schedule one through the SQL Agent.

In practice, clients operate with numerous data formats (excel and xml). In some cases, Import and Export Wizard included in Microsoft SQL Server Management Studio helps greatly. However, I prefer dbForge Data Pump for SQL Server a new SSMS add-in from Devart. The tool allows me to quickly and easily upload and download data in a variety of formats.

Recently, I needed to upload data from a large XML file with quite simple structure:

<users>
<user Email="joe.smith@google.com" FullName="Joe Smith" Title="QA" Company="Area51" City="London" />
</users>

Let’s compare three approaches to resolving the problem: SSIS, XQuery, and Data Pump. We cannot use the SSMS Import and Export Wizard as it does not work with XML.

  1. SSIS

In Integration Service, create a new Data Flow Task:

 SQL SERVER   Easy Way to Import and Export SQL Server Data

On the Data Flow tab, select the XML data source. Then we need to specify the XML file to load data from and generate the XSD schema:

 SQL SERVER   Easy Way to Import and Export SQL Server Data

Since all XML values are stored in the Unicode, I need to convert them to ANSI for the following insertion:

 SQL SERVER   Easy Way to Import and Export SQL Server Data

Then we need to specify where to insert data:

 SQL SERVER   Easy Way to Import and Export SQL Server Data

We need to specify the table to insert data to. In this example, I created a new table.

 SQL SERVER   Easy Way to Import and Export SQL Server Data

Then just press the Execute Results and check the package for errors:

 SQL SERVER   Easy Way to Import and Export SQL Server Data

The SSIS package creation process took 10 minutes. The package ran for 1 minute.

  1. XQuery

Often, writing a query is much faster than creating a SSIS package:

IF OBJECT_ID('dbo.mail', 'U') IS NOT NULL
DROP TABLE dbo.mail
GO
DECLARE @xml XML
SELECT
@xml = BulkColumn
FROM OPENROWSET(BULK 'D:\users.xml', SINGLE_BLOB) x
SELECT
Email = t.c.value('@Email', 'VARCHAR(255)')
,
FullName = t.c.value('@FullName', 'VARCHAR(255)')
,
Title = t.c.value('@Title', 'VARCHAR(255)')
,
Company = t.c.value('@Company', 'VARCHAR(255)')
,
City = t.c.value('@City', 'VARCHAR(255)')
INTO dbo.mail
FROM @xml.nodes('users/user') t(c)

It took me 3 minutes to create the query. However, the execution time exceeded 9 minutes. The reason is that parsing server-side values is quite expensive.

  1. dbForge Data Pump for SQL Server

Now’s the time to try Devart’s Data Pump.

In the Database Explorer shortcut menu, select Import Data.

easytable 07 SQL SERVER   Easy Way to Import and Export SQL Server Data

Then we need to specify the file type and the file path.

easytable 08 SQL SERVER   Easy Way to Import and Export SQL Server Data

Then we can select a table to insert data. The table can be created automatically.

easytable 09 SQL SERVER   Easy Way to Import and Export SQL Server Data

Select the XML tag for parsing:

easytable 10 SQL SERVER   Easy Way to Import and Export SQL Server Data

Now we can select what columns we need (from the selected tag).

easytable 11 SQL SERVER   Easy Way to Import and Export SQL Server Data

Now we need to check the Use bulk insert option to reduce the load on the log file when inserting data.

easytable 12 SQL SERVER   Easy Way to Import and Export SQL Server Data

Click Import and that’s it! The whole process took 2 minutes. This approach does not require any special qualification. The tool allows me to quickly and easily upload data in any formats convenient for my clients.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix: Error: The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”. (SQL Server Import and Export Wizard)

Here is the question received from user – the email was long and had multiple request from reader to resolve this error.

Scenario:

The user was trying to import data from Excel to tables in SQL Server Database. Every time he attempted to import the data he faced following error. He tried using SSIS package as well using the Import Export Wizard (which creates an SSIS package under the hood as well) but he he kept on facing following error. He could not figure out the reason behind the error. I have modified the error to make it readable.

Error:

ssiserror SQL SERVER   Fix: Error: The conversion returned status value 2 and status text The value could not be converted because of a potential loss of data..  (SQL Server Import and Export Wizard)

– Executing (Error)
Messages
Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column “Col1” (18) to column “Col1” (51). The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. [Error Detail]. 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 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Data Conversion 0 – 0” (39) failed with error code 0xC0209029 while processing input “Data Conversion Input” (40). 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)

Solution:

Well, there can be many reasons for this error to show up but the major reason is that data type mismatch. I have observed that following two are the major reason for the error.

1) Null’s are not properly handled either in the destination database or during SSIS package creation. It is quite possible that the source contains a null database but the destination is not accepting the NULL data leading to build generate above error.

2) This is the most common issue I have seen in the industry where data types between source and destination does not match. For example, source column has varchar data and destination column have an int data type. This can easily generate above error. There are certain kind of datatypes which will automatically convert to another data type and will not generate the error but there are for incompatible datatypes which will generate above error.

The best practices is to make sure that you match your data types and their properties of the source and destination. If the datatypes of the source and destination are same, there is very less chance of occurrence of the above error.

Reference: Pinal Dave (http://blog.sqlauthority.com)