CODEWITHSUNDEEP
sql-servercsvssisetlflat-file
user8504877
user8504877

Reputation:

MS SQL Server SSIS Import error decimal value

I have the following csv file

Column
2,3
2,4

And everytime when I import the data via SSIS Packages I get an error because of the comma value. When I change 2,3 and 2,4 to 2.3 and 2.4 it is working fine. And the values will be stored correctly with 2,3 and 2,4 into table.

For import I use Codepage 1252 ANSI-Lateinisch I and the field in the db is decimal(18,2).

What is wrong with my import? Any ideas?

THanks

Upvotes: 4

Views: 2553

Answers (2)

Hadi
Hadi

Reputation: 37313

You have mentioned the error cause in the question:

When I change 2,3 and 2,4 to 2.3 and 2.4 it is working fine. And the values will be stored correctly with 2,3 and 2,4 into table.

Which implies that , is causing the issue. Check that is not used as delimiter.

In the Flat File connection manager, go to Advanced Tab, select the column and change the Column delimiter property.

If it is the last column in the row, change the delimiter to {CRLF}.

If you have many columns in the file and the delimiter is comma , then you have to replace it with . or use add a text qualifier.

Workaround

If it still doesn't work, chande the column type to DT_STR then in the Data flow task add a derived column that replace , with , and cast the value as decimal. Example:

(DT_DECIMAl,2)REPLACE([COLUMN], ",", ".")

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

In your csv connection manager, change the column delimiter to the {CrLf} character.

Upvotes: 1

Related Questions