CODEWITHSUNDEEP
sqlsql-serverssis
Denis1893
Denis1893

Reputation: 65

Can't convert String to Numeric/Decimal in SSIS

I have five or six OLE DB Sources with a String[DT_STR], with a length of 500 and 1252 (Latin) as Code Page. The format of the column is like 0,08 or 0,10 etc etc. As you can see, it is separated with a comma. All of them are equal except one of them. In this one source, I have a POINT as separation. On this it is working when I set the Data Type in the advanced editor of the OLE DB Source. On another (with comma separated) it is also working, if I set the Data Type in the advanced editor of the OLE DB Source. BUT the weird thing is, that it isn't working with the other sources although they are the same (sperated with comma).

I tested Numeric(18,2) and decimal(2).

Another try to solve the problem with the conversion task and/or the derived column task, failed.

I'm using SQL Server 2008 R2

Slowly, I think SSIS is fooling me :)

Has anyone an idea?

/// EDIT Here a two screens:

Is working:
click
enter image description here

Isn't working:
click
enter image description here

Upvotes: 0

Views: 11139

Answers (3)

Jibin Kannembreth
Jibin Kannembreth

Reputation: 1

The Issue arises when there is unhandled space or null. I have worked around using the Conditional (Ternary) Operator which checks the length:

LEN(TRIM([Column Name])) >= 1 ? (DT_NUMERIC,38,8)[Column Name] : 0

Upvotes: 0

Vikramsinh Shinde
Vikramsinh Shinde

Reputation: 2878

Check that there are any spaces in between the commas, so that the SSIS is throwing an error trying to convert the blank space to a number. A blank space does not equal nothing in between spaces.

Redirect error rows and output the data to a file. Then you can examine the data that is being rejected by the SSIS and determine why it's causing error.

Reason for the error

1) Null’s are not properly handled either in the destination database or during SSIS package creation. It is quite possible that the source contains a null database but the destination is not accepting the NULL data leading to build generate above error.

2) Data types between source and destination does not match. For example, source column has varchar data and destination column have an int data type. This can easily generate above error. There are certain kind of datatypes which will automatically convert to another data type and will not generate the error but there are for incompatible datatypes which will generate The value could not be converted because of a potential loss of data. error.

Upvotes: 0

Mike Honey
Mike Honey

Reputation: 15027

I would not set the Data Type in the Advanced Editor of the OLE DB Source. I would convert the data in the SQL Code of the OLE DB Source, or in a Script Transformation e.g. using Decimal.TryParse , which would update a new column.

SSIS is unbeleivably fussy over datatypes and trying to mess with its internals is not productive.

Upvotes: 1

Related Questions