Error by converting string to decimal

Hi,

I'm trying to import a text-file into SQL2005 by using SSIS. Unfortunately it doesn't seem to work in the way I thought. Because I'm using the german version of SQL2005 I need to translate the items of my toolbox, so dont wonder if their names are not exactly the same as in the english versions.

Try number1:

a) My datasource is a flatfile, in the connection manager I changed the DataType for the input column to DT_DECIMAL. In my destination table the data type of ths column is decimal(20,4). Name of the column is "NXT_PUT_PX".

b) Running this package results in this error:

"Fehler bei der Datenkonvertierung. Die Datenkonvertierung für die NXT_PUT_PX-Spalte gab den Statuswert '2' und den Statustext 'Der Wert konnte aufgrund eines m?glichen Datenverlustes nicht konvertiert werden.' zurück. Fehlercode: 0xC0209084. "

Translation of this is something like:

Error by converting data. Converting data for column NXT_PUT_PX returned state '2' and text 'Value couldn't be converted because of a potential loss of data'.

Try number2:

a) same as try number1

b) Adding a datatransformation-task (Datenkonvertierung), and set NXT_PUT_PX to be converted to DT_DECIMAL wirh SCALE 20.

c) Assigning this converted column to the destination column.

d) Running the package results in the same error as described above.

Try number3:

Same as try number 2 but the input column for my flatfile is now DT_WSTR with width 50.

Running this results in the same error message as above, but different errorcode. Errorcode is 0xC020907F.

Any hints how to do this conversion correct?

Best regards,

Jan Wagner

Without looking at the data its difficult to say.

You should try and find the offending data. Following what you did in your "try number 1", put an error output on the flat file source adapter. This error output will contain all values that failed the conversion. You can then look at the failed data either in a data viewer or a flat file destination and we can then work out why the conversion is failing.

-Jamie

|||

Hi Jamie,

thanks again for your fast answer.

Did add a error output as flatfile.

The "errorflatfile" contains all rows of my test inputfile which didn't had a value in column NXT_PUT_PX. I'm wondering about that, because I allowed this column to be NULL and cannot see why there could be potential data loss.

Any ideas?

Regards,

Jan

|||

It sounds as tho you are on the right track.

What do you mean you allowed the column to be NULL? There is no concept of nullability in the SSIS pipeline (because NULLs are always allowed) so I assume you mean you specified columns to be NULLable on the destination but that is irrelevant because the erros occur before the data even gets to the destination.

It sounds as though the problem is happening during the parsing in the flat file source adapter. For that reason, I would import the data as a string and then parse it out yourself using a derived column expression, making sure that you check for NULLs (or empty strings) and handling them accordingly.

-Jamie

|||

You're right, I meant NULLs allowed in my destination table.

I tried handling the column with a derived column, so I now got a new error...

This is my statement for the derived column:

ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The new error is: 0xC0049063. "Fehler beim Bedingungsvorgang". Something like error in condition.

What I try to do with this statement is:

IF NXT_PUT_PX == NULL

THEN CREATE A NEW DT_DECIMAL_NULL

ELSE

CONVERT NXT_PUT_PX TO DT_DECIMAL

In german we would say "Hier ist der Wurm drin". ;-)

Regards,

Jan Wagner

|||

Your expression seems correct for what you want to do.

My suspicion would be that the failure is occuring on the conversion to DT_DECIMAL in the non-null cases. It might be as simple as a value that cannot fit in scale 20, or maybe there are spaces you need to trim or replace? My suggestion would be to configure the error output and send the failing rows to it, and inspect the data to see if that gives you any clues.

Mark

|||

Hmmm, not sure. Looks OK to me!

Keep trying :)

Sorry I can't be of more help!!

-Jamie

|||

So,

here is how it now works in my package:

1. I declared all input source columns which were including decimals as strings as DT_WSTR(30).

2. Replaced all "." with "," in "decimal" columns (thats specific for me, because here in germany we use "," as separator, but my input data come with ".").

3. Converting these columns to DT_R8.

4. Inserting into table with float as datatype for these columns.

5. Seems to work!

Thanks for all your help and ideas.

Best regards,

Jan

|||

You might be able to optimize that a litte and get rid of the explicit string replacement of "." with "," by setting the locale ID on the conversion component to a locale that uses "." for decimal place. Or better yet, set the locale ID on the flat file source component, and set the column type to DT_R8 right there.

Of course, if you have other columns you are reading that require the locale ID to be set, this might not work for you...

|||

Hi Mark,

when I set the locale ID, I get a error message that says that this locale id is not installed on my computer. I will try this again in one of the next packages, due to time pressure on my actual work.

Thanks,

Jan

|||

you committed a very common programming error. the following line:

IF NXT_PUT_PX == NULL

is not the correct way to test for a null value. use the ISNULL function instead.

|||Hi Duane,
I used the following line:
ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The line you are complaining about was just used as "pseudo code" for explaining the algorithmus I wanted to use.

Regards,

Jan