Import export failed : Data conversion failed

[Source - chn_employee_vew_test_txt [1]] Error: Data conversion failed. The data conversion for column "Column 42" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Source - chn_employee_vew_test_txt [1]] Error: The "output column "Column 42" (136)" failed because truncation occurred, and the truncation row disposition on "output column "Column 42" (136)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

I using Locale (People's Republic of China) and code page of 936 (Simplied Chinese GBK) with header row delimiter {CR}{LF}.

I am using flat file import method.

Whenever the server process the Column 42 with value "11,Nanjing Rd.W, China" which contain 'comma' or '.' it will hit error importing with above message. When i manually change the column value to non comma or '.' (11 Nanjing Rd W China) in the flat file it is ok.

I am using SQL server 2005.

Please advise what need to be done to avoid this error ?

Thanks in advance and any idea or suggestion is very much appreciated as i have try to solve this issue for over a week but still not able to find any answer on it.

Please help.

regards,

kong

Check the Quoted_Identifier and text_qualifier values in SSIS, if you are running from a workstation then make sure SSIS server component is installed

import data from excel sheet to sql server

i am using an OPENROWSET method to import from excel data to sql server. i a
m getting an error like
ADO error:[OLE/DB provider returned message: Operation wa canceled.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitializ
Initialize returned 0x80040e4e
Could not initialize data source object of OLE DB provider "MSDASQL'.
Deferred prepare could not be completed
i want to reinstall the OLE DB Driver.can anyone tell me the exact link to d
owload the OLE DB driver.
From http://www.developmentnow.com/g/100...ver-connect.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.comThe provider is installed with MDAC. You can download MDAC
versions and also download component checker to check the
MDAC version from:
http://msdn.microsoft.com/data/ref/mdac/downloads/
The exact link depends on which version you want to
reinstall.
-Sue
On Tue, 01 Aug 2006 10:19:53 GMT, shybi<ajesh@.k.st> wrote:

>i am using an OPENROWSET method to import from excel data to sql server. i
am getting an error like
>ADO error:[OLE/DB provider returned message: Operation wa canceled.]
>OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitializ
>Initialize returned 0x80040e4e
>Could not initialize data source object of OLE DB provider "MSDASQL'.
>Deferred prepare could not be completed
>i want to reinstall the OLE DB Driver.can anyone tell me the exact link to
dowload the OLE DB driver.
>From http://www.developmentnow.com/g/100...elopmentnow.com|||I think you want to be using a different provider instead of "MSDASQL."
Try: Microsoft.Jet.OLEDB.4.0
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"shybi" <ajesh@.k.st> wrote in message
news:13b25c23-b877-4ac8-92dd-2d242c204a77@.developmentnow.com...
>i am using an OPENROWSET method to import from excel data to sql server. i
>am getting an error like
> ADO error:[OLE/DB provider returned message: Operation wa canceled.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitializ
> Initialize returned 0x80040e4e
> Could not initialize data source object of OLE DB provider "MSDASQL'.
> Deferred prepare could not be completed
> i want to reinstall the OLE DB Driver.can anyone tell me the exact link to
> dowload the OLE DB driver.
> From
> http://www.developmentnow.com/g/100...ver-connect.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com