A 99.99 numeric from flat file doesn't want to go in a NUMERIC(4,2)'SQL Server

Question

I have a csv file :

1|1.25
2|23.56
3|58.99

I want to put this value in a SQL Server table with SSIS. I have created my table :

CREATE TABLE myTable( ID int, Value numeric(4,2));

My problem is that I have to create a Derived Column Transformation to specify my cast :

(DT_NUMERIC,4,2)(REPLACE(Value,".",","))

Otherwise, SSIS don't seem to be able to put my Value in my column, and fill my column with null value.

And I think it is tooooo ugly to do it this way. I want my Derived Column Transformation be here for real new derived column, and not some simple cast that I think SSIS have to detect.

So, what is the standard way to use SSIS to resolve this problem ?

Answer 1

Can you change your SSIS source column to be in the correct datatype?

Answer 2

If you have control over the production of your file, I'd suggest you to format values without ANY decimal or thousand separation : in this case I'ld have a file with values:

1|125  
2|2356  
3|5899

and then apply a division by 100 when importing the data. While it has the advantage of being culture-independent, of course it has some drawbacks:
1) First of all, it may not be possible to impose this format of the file.
2) It presumes that all numeric values are formatted accordingly, in this case every value is multiplied by 100; this can be an issue if you have to mix values from countries with different decimal positions (many have two decimals, but some have zero decimals).
3) It may severely impact with other routines, maybe out of your control
Therefore, this can really be an option if you have total control on the csv file.

Answer 3

You're loading this up in international format (56,99 in lieu of 56.99). You need to load this as 56.99 for SQL Server to recognize it as such. Take out the REPLACE(Value, ".", ",") and just have the code be:

(DT_NUMERIC,4,2)(Value)

Handle the formatting on the application side, not on the data side. The comma is a reserved operator in SQL Server and you can't change that fact.

Answer 4

Haven't used SSIS a whole lot, but can't you set the regional settings on the File Source or at least set the decimal separator?

Answer 5

BULK
INSERT myTable
FROM 'c:\csvtest1.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)

csvtest1.txt

1|1.25
2|23.56
3|58.99