I am trying to use the Import & Export Data Wizard to import the contents of a CSV file into a table in MSSQL.
I have no issues if I import all columns as strings however, I need one column to be a float. (In the CSV, column 4 is a currency amount with two decimals e.g. $200.30).
Whenever I set the value of column 4 to a float, I receive the following error during the import:
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data
conversion for column "Column 4" returned status value 2 and status
text "The value could not be converted because of a potential loss of
data.". (SQL Server Import and Export Wizard)
Here is an example of the data which I am importing:
+----+------+----------+-----------+--------+
| 1 | 2014 | December | 400089323 | 8.03 |
| 2 | 2014 | December | 400319749 | 45.65 |
| 3 | 2014 | December | 400377067 | 38 |
| 4 | 2014 | December | 400524693 | 0 |
| 5 | 2014 | December | 400536070 | 125.31 |
| 6 | 2014 | December | 400663546 | 266.95 |
| 7 | 2014 | December | 400705907 | 218 |
| 8 | 2014 | December | 400763025 | 244.39 |
| 9 | 2014 | December | 400782510 | 12.89 |
| 10 | 2014 | December | 400794040 | 61.04 |
+----+------+----------+-----------+--------+
This is the datatype of column 4:
The rest of the columns are "numeric [DT_NUMERIC]" except column 2 which is "string [DT_STR]".
I have tried different datatypes for column 4 such as numeric & currency. This did not work.
FYI: I am using SQL Server 2012.
Best Answer
Import it as a string and do a derived column to convert it to a float. Whatever rows fail conversion will be re-directed to the error output and you can at least see what the problem is.
Flat File connections fail completely when they encounter problems. It's generally more productive to bring them in as loosely as possible and adding structure inside SSIS.
You almost certainly don't want to be using floats anyway. Use 'numeric' once they're inside the package.