Error message when importing data into SQL (Import & Export Data Wizard)

Question

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.

Answer 1

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.

Answer 2

Just want to share a file from Microsoft which has helped me to allow an easier import. I believe using this method will get around some of the conversion errors which may be difficult to locate and do not seem to refresh using the WIZARD.

Hate to use StackExchange as a director to another tutorial, but in general I would say the documentation from Microsoft is relatively up to date and may be helpful.

https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/get-started-with-this-simple-example-of-the-import-and-export-wizard?view=sql-server-ver16

The specific tip that helped me was to use the Excel format and then the .Net Framework Data Provider for SqlServer. This was appropriate since I am only working with the data on my local machine. Then change the InitialCatalog to your desired database location for the import.