Solved

Sql Server Import and export

Posted on 2014-03-23
9
1,878 Views
Last Modified: 2014-04-30
I am trying to import from an excel spreadsheet  into a sql server table. However, It fails because of one of the columns in the excel spreadsheet is  a value that needs to be converted to a uniqueidentifier.  The values in the spreadsheet look like this
0EC16729-5097-41CD-9556-0003BAE5954A

Error Messages
Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column "Vehicle_ID" (15) to column ""Vehicle_ID" (75).  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)

How do I get around that using the sql server import and export tool. See attachment where it shows you an option to modify the query to do data transfer.
sql-server-import-and-export-wiz.PNG
0
Comment
Question by:metro156
9 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39949345
I don't think the dialog you have quoted is relevant to the problem - you are getting a failure when the key is being loaded and the error message implies the table definition does not match the content of the excel column.

first I would try removing the dashes from the excel data, the example looks as though it should be a hex number and the dashes will make it appear as a string.
0
 
LVL 77

Expert Comment

by:arnold
ID: 39949359
Your error points out that the column width in the database is 15, while the length of the data you are importing is 75.

One option is to alter the table column to be 75 characters in length, the other option I think the wizard has an option you can specify that it is ok to truncate the data in the column.

Bcp is the command line utility that can be used to import a file while on the relevant columns use the allow truncate or use substring .........
0
 
LVL 18

Expert Comment

by:Dennis Aries
ID: 39949589
The error states the field lengths 15 and 75. Neither is a length used in Guids (which is 32). I think your view on the Excel-table is wrong. Can you verify that?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:metro156
ID: 39963024
I will look at the possible solutions in the next 3 days and give a response.
0
 

Author Comment

by:metro156
ID: 39973888
I need more time to evaluate. This week end I will look at.
0
 

Author Comment

by:metro156
ID: 39981752
Instead of using a excel spreadsheet , I tried importing a table with one column of varchar to a table with the column as UniqueIdentifier and it still didn't work. I just don't understand why the conversion does not work in Sql Server Import and Export. it certainly works when I just T-SQL in a new query window and by simply just doing a regular insert of a string into a uniqueIdentifier and it converts right away.

So why doesn't the SQL Server Import and Export Tool work????


See error below:
 Executing (Error)
Messages
Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column "PID_NO" (17) to column "PID_NO" (45).  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.  The "output column "PID_NO" (45)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "PID_NO" (45)" 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 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Data Conversion 0 - 0" (37) failed with error code 0xC0209029 while processing input "Data Conversion Input" (38). 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)
0
 

Author Comment

by:metro156
ID: 39981998
Taking out the dashes  out the string does not resolve the issue of load from an excel spreadsheet.
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39982045
0
 

Author Comment

by:metro156
ID: 39987487
I am going to try that within the next couple of days.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Cannot sign into Lync with non domain windows 10 machine 3 94
Batch convert .doc to .docx 13 555
Using MS Code on my Mac 6 68
PL SQL Developer 7 52
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question