Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jarrodr
Starting Member
11 Posts |
 Posted - 2008-10-13 : 03:59:43
|
I output information to a text file, it is in unicode and works perfectly but when I try output in ANSI I get an error. Whats the problem?javascript:insertsmilie(' ')Error: 0xC02020A1 at MTD, MTD [1727]: Data conversion failed. The data conversion for column "Sdata" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".Error: 0xC02020A0 at MTD, MTD [1727]: Cannot copy or convert flat file data for column "Sdata".Error: 0xC0047022 at MTD, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "MTD" (1727) failed with error code 0xC02020A0. 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.Error: 0xC0047021 at MTD, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC02020A0. There may be error messages posted before this with more information on why the thread has exited. |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2008-10-13 : 08:40:53
|
| The error is basically telling you that SSIS isn't prepared to implicitly convert characters in the Sdata column that can't be represented in the 'ANSI' output code page.In the SELECT that gets the data for output you can explicitly cast Sdata as a varchar (or char, text) with an appropriate collation that uses the 'ANSI' code page you're outputting the data in. This will cause characters that can't be represented in the output code page to appear in the output as the 'nearest' representable character (or a question mark if there are no near characters). |
 |
|
|
Jarrodr
Starting Member
11 Posts |
Posted - 2008-10-13 : 08:47:49
|
| Thanks Arnold, there is my select code...select Sdata from MISorder by ID, tr_dte, Sdata;I am new to this, how would I cast? |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2008-10-13 : 09:22:23
|
| It depends what Sdata is. Supposing it's an nvarchar(100) and that your output is Windows code page 1252 ('Western') then use one of the Latin1_General collation to force the code page and cast it to varchar(100) like this:SELECT CAST(Sdata COLLATE Latin1_General_CI_AS AS varchar(100)) AS Sdata |
|
|
|
Jarrodr
Starting Member
11 Posts |
Posted - 2008-10-14 : 00:51:22
|
| Thanks Arnold, I will try this and get back you |
|
|
|
Jarrodr
Starting Member
11 Posts |
Posted - 2008-10-14 : 02:19:04
|
| It didn't work...This is my sql code:select cast(a.sData AS varchar(200)) as sdata1from BPMIS awhere left(sData,2) not in ('30','31')order by ID, tr_dte, sData;It gives me the same error. First I tried your exact code and that didn't work. |
|
|
|
|
|
|
|
|
Output text file must be ANSI not UNICODE?