Solved

Sql Server Import and export

Posted on 2014-03-23
9
1,799 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 76

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
 

Author Comment

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

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now