3

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.

3
  • 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, 2015 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, 2015 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, 2015 at 22:25

2 Answers 2

4

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.

0

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://docs.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.

screenshot from https://docs.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

1
  • You could make "this forum" more helpful by including the salient points from your link into the answer itself, lest it becomes useless when (not if) the link goes stale.
    – mustaccio
    Jun 24 at 16:27

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.