Solved

Sql Server Import and export

Posted on 2014-03-23
9
1,964 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 78

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
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…
Viewers will learn the different options available in the Backstage view in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

691 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