July 15, 2010 at 12:29 pm
Hi everyone,
I am trying to import data from .xlsx excel file but i am getting this error:
There was an error with output column "comments" (121) on output "OLE DB Source Output" (37). The column status returned was:
"Text was truncated or one or more characters had no match in the target code page.".
The excel file is from oracle and the datatype for "comments" column is varchar2(2000byte) in oracle and varchar(2000) in sql server.
when i serched for the solution someone told me to convert excel to flat file and i did that and created a package again but now i got this error:
[Flat File Source [1571]] Error: Data conversion failed. The data conversion for column "COMMENTS" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Flat File Source [1571]] Error: The "output column "COMMENTS" (1672)" failed because truncation occurred, and the truncation row disposition on "output column "COMMENTS" (1672)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Flat File Source [1571]] Error: An error occurred while processing file "C:\Documents and Settings\Desktop\Copy of ABCD.txt" on data row 201.
Can anyone please tell me what i need to do if i get the above error,
shall i continue using excel or flat file as source?now both errors i am getting are from the source....
July 16, 2010 at 2:46 am
In your Excel source, go to the advanced properties and check the datatype of your output column "Comments".
Chances are that is has a datatype with a lenght shorter then 4000, resulting in truncation.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 16, 2010 at 9:57 am
I don't see anything in advanced properties.
But when i searched the web for this error some one told that if the source column has more than 255 characters we will get this error because excel doesn't support the column >255 characters.
July 16, 2010 at 12:35 pm
xlsx(office 2007), will support upto 4000 characters sql-->xls(x)-->sql (import /export),
earlier versions will support till 256 chars, it was one of the issue,
I just tried exporting from SS 2008 --> office 2007, was exported 4000 chars max
and also tried exporting from SS 2005 --> office 2005, was exported 256 chars max
July 16, 2010 at 1:24 pm
OK.
i don't know what is causing the problem,because i have changed all the input column datatypes using derived task ->advanced editor.
except the date column which is date data type in excel and date timestamp in sql server.
and the visual studio is taking time to open the package too.even if i want to edit the task it is taking 10 min to open the tasks in sql 2008 r2(BIDS).I have been working on this for the past 3 days because of the delay i am getting .
The excel i am using to import data into sql is in .xlsx 2007,so i have changed the provider for connection manager ,since then i am seeing the delay in Microsoft visual studio.
can any one please help me ?
July 18, 2010 at 3:50 pm
Forgot to mention this,
I have sql server 2005 on my computer and recently i have installed sql server 2008 client tools.
sql 2008 has separate server and worked with 2008 ssis and the performance is good to me.
but, now i need to work with the old servers so i created a package in sql server 2005 to import excel into sql server 2005 ...
Is this causing the delay ?i shouldn't have two versions on one computer(2005 and 2008)
Can anyone please tell me ?i have been working on this since one week.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply