| Author |
Topic |
|
ilimax
Posting Yak Master
164 Posts |
 Posted - 2012-08-10 : 16:01:01
|
| HELP !!!I have .net application for importing data to SQL sever .. It worked fine before, but now suddenly started to throw errors ...Application uses references to SQL Server DTS package and import data by Package.Execute method ... SubComponent : Flat File Source [1]Description : Data conversion failed. The data conversion for column "COL1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". SubComponent : Flat File Source [1]Description : The "output column "COL1" (30)" failed because truncation occurred, and the truncation row disposition on "output column "COL1" (30)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. SubComponent : Description : SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. |
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-08-10 : 16:19:17
|
| What is crazy here .... If I load flat file with SQL Server Import Wizard I do not get any error and I import file good ...This COL1 is varchar(500) ...Program works fine for many other files. It fails only for couple files with same errors.I think error happens if fields are bigger ... But data in flat file is OK because I am able to import by SQL Serve import Wizard |
 |
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-08-23 : 09:44:28
|
| I am still waiting for replay ..If anybody have any idea why I have issue with .net application please let me know ... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-24 : 01:11:32
|
| if flat file source delimited? are you sure it doesnt have any spurious rows------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-08-28 : 22:36:30
|
| 000439|Dcca xxxxx|2|0001|0749|Ixxxeaxxd rxxk of sxxious or fatal hepatotoxicity.000244|Dcxaxxxxx|2|0001|0444|Inxxeased rxxk of adverse events in premature infants include hyxxxxension, irritability, vomiting, abdominal distension, heart block. Axxitional risk of seizure in prxxature infants with unxxlying Ccc condition (e.g. previous seizure, peri-natal asxxyxia). Contains benzyl alcohol.000445|Dcxxxxx|2|0001|6449|Poxxxxle ixxreased suxxidal ideation & attempt in pexxxrixs with major deccession. Not recoccecced for use in pexxxxrics age < 12 years.000646|Dcxoxxxxin|3|0001|6569|Rcck of decaced cardiocccccity and heart failure, prepubertal growth failure, sccondary AML, other maliccancy.000647|Dcxxxxxine|1|0001|0729|CcS eccicacion and coccuccion rcck in newborns. Not recommended age <2 years. |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-08-28 : 22:39:58
|
| Here are 5 rows of data .. second one is that make error ... Last column caused problem .. Seems that large data makes trouble ... Row looks fine .. There are 5 columns delimited by "|" ...Any idea why I have problem??? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-29 : 14:39:19
|
| try adding a derived column transform and return LEN(data) as a new column for fifth column and see if it is > 500------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-08-30 : 14:31:27
|
| I have TRIM function setup ... so I trim the ends ... Not sure it is that issue ..I start to think there is carriage return character somewhere ... But now, I do not know how to replace it ..This one does not work .. CHAR fucntion is not receognized ....(DT_STR,{1},1252)(TRIM(REPLACE([{0}],CHAR(13),'')) |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-08-30 : 14:33:03
|
| Forgot to tell you, if I remove this sick row (seond one with 000244 value) from text file, import works fine ...Actually there are 3 that causes errors .. I remove all 3 and works fine ...So, I think REPLACE will help me a lot of I can setup |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-30 : 16:08:30
|
quote:
Originally posted by ilimax
I have TRIM function setup ... so I trim the ends ... Not sure it is that issue ..I start to think there is carriage return character somewhere ... But now, I do not know how to replace it ..This one does not work .. CHAR fucntion is not receognized ....(DT_STR,{1},1252)(TRIM(REPLACE([{0}],CHAR(13),''))
TRIM doesnt do truncate of data but it just removes leading trailing spacesso if you've valid data over 500 chars it will still break------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-30 : 16:11:14
|
quote:
Originally posted by ilimax
Forgot to tell you, if I remove this sick row (seond one with 000244 value) from text file, import works fine ...Actually there are 3 that causes errors .. I remove all 3 and works fine ...So, I think REPLACE will help me a lot of I can setup
nope i still thing issue is with value of last column in those rows as they're long and exceeds maxlength set for target fieldso you should be adding an expression to do LEFT or SUBSTRING on them to truncate them at 500 chars and replace column with this expression if you want to succesfully importALternatively go to target table and increase length from 500 to whatever higher value and refresh the metadata in export import package------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-08-30 : 18:14:28
|
| Thank you for replay ...When I copy data of that bad row into MS word and check by counter .. It is telling me 249 chaacters with spaces ...This is exact data .. I just replaced some letters with xxx ... I did not want to post here company data ... I was thinking TRIM will trim empty space on the end ....hm ...Inxxeased rxxk of adverse events in premature infants include hyxxxxension, irritability, vomiting, abdominal distension, heart block. Axxitional risk of seizure in prxxature infants with unxxlying Ccc condition (e.g. previous seizure, peri-natal asxxyxia). Contains benzyl alcohol. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-30 : 21:24:28
|
quote:
Originally posted by ilimax
Thank you for replay ...When I copy data of that bad row into MS word and check by counter .. It is telling me 249 chaacters with spaces ...This is exact data .. I just replaced some letters with xxx ... I did not want to post here company data ... I was thinking TRIM will trim empty space on the end ....hm ...Inxxeased rxxk of adverse events in premature infants include hyxxxxension, irritability, vomiting, abdominal distension, heart block. Axxitional risk of seizure in prxxature infants with unxxlying Ccc condition (e.g. previous seizure, peri-natal asxxyxia). Contains benzyl alcohol.
Then it might be hard space. try adding an expression to replace them using REPLACE function. they correspond to CHAR(160)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-08-30 : 23:06:09
|
| That is where I have hard time now .. I am getting error that CHAR function does not exist ...(DT_STR,{1},1252)(TRIM(REPLACE([{0}],CHAR(13),''))Here is my full string.format play ...strExpression = String.Format("(DT_STR,{1},1252)(TRIM(REPLACE([{0}],CHAR(13),'')) == """" ? NULL(DT_WSTR,5):([{0}]))", objColumn.Name, Convert.ToInt32(objColumn.Length)) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-31 : 10:33:49
|
| cant you do it t-sql using execute sql task?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-08-31 : 21:55:31
|
| Not sure what you mean ... I am trying to fix .net application for importing data ...App works perfect, but fails sometimes with some data ...Application use reference to SQL Server files and there is Package.Execute call in the application ... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-31 : 22:46:13
|
quote:
Originally posted by ilimax
Not sure what you mean ... I am trying to fix .net application for importing data ...App works perfect, but fails sometimes with some data ...Application use reference to SQL Server files and there is Package.Execute call in the application ...
i was suggesting to change package.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-09-04 : 09:27:52
|
| I did ....but it is same ... Dim objPackage As New Package() objPackage.Name = "MoveDataToDatabase2" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-05 : 22:10:33
|
| hmm..what does that mean? did you add expression inside package to strip off the hard space?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2012-09-06 : 09:58:32
|
| No .. that is my problem .. I do not know how to do it ...If I do Replace(string,CHAR(xx) ...) it is telling me CHAR function does not exist.Any idea how I can replace hard space or any character...???(DT_STR,{1},1252)(TRIM(REPLACE([{0}],CHAR(13),'')) |
|
|
|
newwaysys
Starting Member
9 Posts |
Posted - 2015-04-09 : 04:25:40
|
| Posted - 08/10/2012 : 16:01:01 Show Profile Email Poster Reply with QuoteHELP !!!I have .net application for importing data to SQL sever .. It worked fine before, but now suddenly started to throw errors ...Application uses references to SQL Server DTS package and import data by Package.Execute method ...SubComponent : Flat File Source [1]Description : Data conversion failed. The data conversion for column "COL1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".SubComponent : Flat File Source [1]Description : The "output column "COL1" (30)" failed because truncation occurred, and the truncation row disposition on "output column "COL1" (30)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.Forgot to tell you, if I remove this sick row (seond one with 000244 value) from text file, import works fine ...Actually there are 3 that causes errors .. I remove all 3 and works fine ...unspammedSo, I think REPLACE will help me a lot of I can setup |
|
|
|
Next Page
|
Import in .Net with Package.Execute