SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Import in .Net with Package.Execute
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ilimax
Posting Yak Master

Bosnia and Herzegovina
164 Posts

Posted - 08/10/2012 :  16:01:01  Show Profile  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.

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

Bosnia and Herzegovina
164 Posts

Posted - 08/10/2012 :  16:19:17  Show Profile  Reply with Quote
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

Bosnia and Herzegovina
164 Posts

Posted - 08/23/2012 :  09:44:28  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 08/24/2012 :  01:11:32  Show Profile  Reply with Quote
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

Bosnia and Herzegovina
164 Posts

Posted - 08/28/2012 :  22:36:30  Show Profile  Reply with Quote
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.

Edited by - ilimax on 08/28/2012 22:42:19
Go to Top of Page

ilimax
Posting Yak Master

Bosnia and Herzegovina
164 Posts

Posted - 08/28/2012 :  22:39:58  Show Profile  Reply with Quote
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???

Edited by - ilimax on 08/28/2012 22:44:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/29/2012 :  14:39:19  Show Profile  Reply with Quote
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

Bosnia and Herzegovina
164 Posts

Posted - 08/30/2012 :  14:31:27  Show Profile  Reply with Quote
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

Bosnia and Herzegovina
164 Posts

Posted - 08/30/2012 :  14:33:03  Show Profile  Reply with Quote
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

Edited by - ilimax on 08/30/2012 14:35:49
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/30/2012 :  16:08:30  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 08/30/2012 :  16:11:14  Show Profile  Reply with Quote
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

Bosnia and Herzegovina
164 Posts

Posted - 08/30/2012 :  18:14:28  Show Profile  Reply with Quote
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.

Edited by - ilimax on 08/30/2012 18:15:17
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/30/2012 :  21:24:28  Show Profile  Reply with Quote
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

Bosnia and Herzegovina
164 Posts

Posted - 08/30/2012 :  23:06:09  Show Profile  Reply with Quote
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))



Edited by - ilimax on 08/30/2012 23:06:48
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/31/2012 :  10:33:49  Show Profile  Reply with Quote
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

Bosnia and Herzegovina
164 Posts

Posted - 08/31/2012 :  21:55:31  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 08/31/2012 :  22:46:13  Show Profile  Reply with Quote
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

Bosnia and Herzegovina
164 Posts

Posted - 09/04/2012 :  09:27:52  Show Profile  Reply with Quote
I did ....but it is same ...

Dim objPackage As New Package()
objPackage.Name = "MoveDataToDatabase2"

Edited by - ilimax on 09/04/2012 09:28:27
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/05/2012 :  22:10:33  Show Profile  Reply with Quote
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

Bosnia and Herzegovina
164 Posts

Posted - 09/06/2012 :  09:58:32  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000