2

I got a CSV like below. The column got some empty rows and the CSV gives the values as a string. But I want to make it a float like ‘3.00000’ with 5 decimals. First, I need to replace the ‘,’ symbol and replace the null values I used this derived column expression: (DT_R8)REPLACE(ISNULL(Col1) ? "0.0000" : Col1,",",".") but it is not working. Do you guys know a better expression? The column is like below:

+---------+
|  Col1   |
+---------+
| 3,00000 |
|         |
| 4,00000 |
| 6.56565 |
|         |
| 4.54545 |
+---------+
Aercheon
  • 101
  • 5
  • 1
    "is not working" is not a problem statement. Is it throwing an error? Is it failing to replace the commas? Is it failing to convert the values to decimals? Is it no handling the empty rows correctly? _What_ is not working? – Eric Brandt Dec 09 '19 at 15:42
  • Error: 0xC0049064 at Data Flow Task, Derived Column [2]: An error occurred while attempting to perform a type cast. – Aercheon Dec 09 '19 at 16:06
  • Error: 0xC0209029 at Data Flow Task, Derived Column [2]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. – Aercheon Dec 09 '19 at 16:06
  • Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (2) failed with error code 0xC0209029 while processing input "Derived Column Input" (3). The identified component returned an error from the ProcessInput method. – Aercheon Dec 09 '19 at 16:07
  • Type cast error, sorry for the wrong info – Aercheon Dec 09 '19 at 16:07
  • In general, be careful with float. Use numeric, if possible. Reason... FLOAT is an estimate, and the number one can be stored as 1.00000000000001 or .99999999999 which obviously is not 1. – KeithL Dec 09 '19 at 17:04
  • @Aercheon can you please mark the answer below as answer if it answered your question? Thanks. – BICube Jan 06 '20 at 02:20

1 Answers1

3

I believe that you will need to look at two things here. First, it is likely that the cells with a comma in them are recognized as strings and therefore they will use a text qualifier of quotes. If you don't explicitly set that up in your SSIS, then SSIS will keep the quotes and then you won't be able to convert that string to numeric. To confirm, on your connection manager, check your preview, if your numbers have a double quotes as below, then you need to set the text qualifier to a quote.

Snapshot before setting the text qualifier:

enter image description here

Now, if that's the case, you need to go to General, and make sure you text qualifier is set to " as below

enter image description here

Once done, you can verify that your preview is now fixed enter image description here

The issue that you are having is assuming that you are dealing with NULL values. This isn't the case when your column is of type string imported from a flat file. You are dealing with an empty string here. So, you need to modify your expression to be:

(DT_R8)REPLACE(Col1 == "" ? "0.0000" : Col1,",",".")

Finally, do you recognize that replacing the "," with "." will result in "3,00000" being converted to 3.0 ? Please make sure that this is the expected behavior.

Following the above will result in the following derived column

enter image description here

BICube
  • 4,451
  • 1
  • 23
  • 44