?
Solved

Sql Server Import and export

Posted on 2014-03-23
9
Medium Priority
?
2,096 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 81

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
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 …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

850 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