2

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:

enter image description here

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.

  • What's the structure of the table you are inserting into? Also is there any point at which the data isn't a numeric value? Like if you import it as string and then try to cast that column as float does that work? – Zane Feb 5 '15 at 18:12
  • The table is created as a new table during the import. The datatypes are always consistent. (A numeric value never shares a column with a string). – pgunston Feb 5 '15 at 22:22
  • I repeat "if you import it as string and then try to cast that column as float does that work?" – Zane Feb 5 '15 at 22:25
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.