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.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Import in .Net with Package.Execute

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
Go to Top of Page

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 ...
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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???
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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),''))
Go to Top of Page

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
Go to Top of Page

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 spaces

so if you've valid data over 500 chars it will still break

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 field
so 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 import

ALternatively go to target table and increase length from 500 to whatever higher value and refresh the metadata in export import package

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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


Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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






Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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"
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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),''))
Go to Top of Page

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 Quote
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.
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 ...

unspammed
So, I think REPLACE will help me a lot of I can setup
Go to Top of Page
    Next Page

- Advertisement -