none
Help, "The value could not be converted because of potential loss of data"

    Question

  • Hi,

     

    I am getting the error below in my Flat File Source.

     

    I've seen this error many times before, and have successfully resolved this problem in the past.

     

    However, this time it's a little different. It's complaining about row 7 of myFile.csv, column 20. I have column 20 defined as a Numeric(18,6). It also maps to the Price field in the table, which is also a Numeric(18,6).

     

    The problem is, on row 7 of myFile, column 20 is blank. That is, there's no data for row 7, column 20.

     

    So, why should it care about this?? If it's blank, then how can you lose any data?? I have several other blank columns in this file, but they aren't throwing any errors. Just this one.

     

    Thanks

     

    Errors:

     

    [Flat File Source - myFile [1]] Error: Data conversion failed. The data conversion for column "Column 20" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

     

    [Flat File Source - myFile [1]] Error: The "output column "Price" (333)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Price" (333)" specifies failure on error. An error occurred on the specified object of the specified component.

     

    [Flat File Source - myFile [1]] Error: An error occurred while processing file "d:\myDir\myFile.CSV" on data row 7.

     

    Tuesday, June 19, 2007 9:07 PM

Answers

  • Ok, mystery solved. And this was my fault for not seeing it sooner.

     

    The data is NOT:

     

    0.00,,,

     

    It was:

     

    0.00, , ,

     

    The difference being that there were spaces in between the commas, so that the flat file was throwing an error trying to convert the blank space to a number. That is my best guess at least. A blank space does not equal nothing in between spaces.

    Monday, June 25, 2007 8:51 PM

All replies

  • This is what part of the data for row 7 looks like:

     

     0.000, , , , , , ,

     

    col 19 = 0.000

    col 20 is the one after that one

     

    Tuesday, June 19, 2007 9:12 PM
  • Try bringing it as a string, then convert it in the data flow.
    Wednesday, June 20, 2007 1:49 AM
  • Is this a normal problem with numbers being missing in flat file? I have not encountered this before.

     

    Would you recommend a script for the conversion?

     

    Thanks

    Wednesday, June 20, 2007 9:40 PM
  • If the number is missing, how is SSIS supposed to interpret it?

     

    I'd bring the column in a string in the flat file connection manager, then use a Derived Column to convert it.

    Thursday, June 21, 2007 12:47 AM
  • Hi sadie,

     I've got through this and followed Bob's steps for solution. Quite a Laborious work if you have hundreds of columns having similar problem.

    1) Go to Destination Mapping options, you can check the Type of the Destination column (with which you are getting the above error) just by mouse over.

    2) Also have a look at the type of the Corresponding Source column, if it doesnot match, go to advanced editor options of the Flat File Source, Click Input and Output Properties -> Output column change the Type to match the Destination Column.

     

    Thanks

    Subhash Subramanyam

     

     

    Thursday, June 21, 2007 5:13 AM
  • Hi Subash,

     

    I'm not exactly sure what you're recommending here?

     

    Are you suggesting this as a workaround? Or just a way to make the "laborious" easier?

     

    Thanks

    Thursday, June 21, 2007 5:40 PM
  •  

    I find it rather unsettling to just now discover that if a flat file is missing a numeric value this will cause an error!

     

    I find it unsettling because I have set up several packages where they read financial data extracts, which are all about numbers! (fortunately nothing is in production yet)

     

    So far, I had not run into this problem until now. Does this mean I should err on the side of paranoia and read ALL numbers from flat files as STRINGS first, and make sure they're numbers? Then convert to the proper data type?

     

    Any advice on best practices in this situation greatly appreciated!!!

     

     

     

    Friday, June 22, 2007 6:09 PM
  • Sadie,

     

    have you set the "Use NULLs..." flag on the Flat File source. Actually, is it OK to treat this value as NULL?

     

    Thanks,

    ~Bob

    Saturday, June 23, 2007 6:13 AM
  • Sadie,

       I find this as a work around in my case but it's laborious incase if we have many such columns for amending types manually.

    Thanks

    Subhash Subramanyam

    Saturday, June 23, 2007 6:27 AM
  • Sadie,

     In the worst case, I've a best solution, Keep all your Erring FlatFile extracts to work in Backward Compatibility Mode.(Embedding the Extracts into  Execute DTS 2000Task)

     

    Thanks

    Subhash Subramanyam

    Saturday, June 23, 2007 6:52 AM
  • I also working on flat files where in we are reading all values as strings and then use a seperate DFT where in I perform data conversion as required.This not only modularize the code but it also helps us to log erronoeus records properly if there is any conversion errors.
    Monday, June 25, 2007 9:09 AM
  • I am following the method of reading all columns as string and thereafter using an DFT to convert to respective types.This not only does modularize your code but also helps you to log errors that might pop up during Data conversion.
    Monday, June 25, 2007 9:11 AM
  • When you say "DFT" do you mean a script that checks each value individually?

     

    I would be curious to see an example of this because I don't know what you mean exactly.

     

    Thanks

    Monday, June 25, 2007 6:06 PM
  • DFT = Data Flow Task
    Monday, June 25, 2007 6:08 PM
  • Hi Bob,

     

    I was really hoping your suggestion would work, but I am STILL getting this error.

     

    Even this link suggests that "Use NULLs..." flag should work:

     

    http://wiki.sqlis.com/default.aspx/SQLISWiki/RetainNullsPropertyOfFlatFileSourceAdapter.html

     

    ??

     

    Any ideas appreciated

    Monday, June 25, 2007 7:19 PM
  • Something is very fishy here.

     

    In an earlier post, someone said "how is ssis supposed to interpret the value if it's missing?"

     

    My answer is, I don't know, but it's never had a problem interpreting it until now.

     

    To prove that there's something weird going on, I created a simplied data file, data flow, and a table in the db:

     

    Sample data:

     

    Name, Address, BankAcct#, BalanceBeg, BalanceEnd
      Joe,1234 Main St,0129384,,100
      Mary, 55 NW Ave,0127584,,
      Sam, 2 Turlock,0463847,200.00,200.00

     

    As you can see, BalanceBeg is missing for Joe and Mary, and BalanceEnd is missing for Joe.

     

    In the flat file conn mgr, BalanceBeg and BalanceEnd are defined as DT_NUMERIC (10,2) as it is in the table.

     

    When I run my data flow, it runs perfectly, no errors, whether I have "NULLs" set or not.

     

    Here's my table data, both with and without NULLS set. In the case of no NULLS, it just inserts 0's instead of NULLs.

     

    Name  Address  BankAcct#  BalanceBeg  BalanceEnd
      Joe 1234 Main St 0129384 NULL 100.00
      Mary  55 NW Ave 0127584 NULL NULL
      Sam  2 Turlock 0463847 200.00 200.00
      Joe 1234 Main St 0129384 0.00 100.00
      Mary  55 NW Ave 0127584 0.00 0.00
      Sam  2 Turlock 0463847 200.00 200.00

     

    CREATE TABLE [Test] (

    [Name] VARCHAR(50),

    [ Address] VARCHAR(50),

    [ BankAcct#] VARCHAR(50),

    [ BalanceBeg] numeric(10,2),

    [ BalanceEnd] numeric(10,2)

    )

     

    So, sorry, I do not buy the theory that ssis doesn't know what to do with missing numeric values. My example proves otherwise. And converting to strings first seems like a lot of work when it seems that ssis has the built-in capability to handle missing numeric values.

     

    Of course, that does not explain the error that I'm continuing to see. I will have to investigate further.

     

     

    Monday, June 25, 2007 7:48 PM
  • Ok, mystery solved. And this was my fault for not seeing it sooner.

     

    The data is NOT:

     

    0.00,,,

     

    It was:

     

    0.00, , ,

     

    The difference being that there were spaces in between the commas, so that the flat file was throwing an error trying to convert the blank space to a number. That is my best guess at least. A blank space does not equal nothing in between spaces.

    Monday, June 25, 2007 8:51 PM
  •  sadie519590 wrote:

    A blank space does not equal nothing in between spaces.



    Right!
    Monday, June 25, 2007 8:53 PM
  •  sadie519590 wrote:

    So, sorry, I do not buy the theory that ssis doesn't know what to do with missing numeric values. My example proves otherwise. And converting to strings first seems like a lot of work when it seems that ssis has the built-in capability to handle missing numeric values.

     

     

    Sadie, I'm sorry if my comment mislead you. I was making a more general statement, not specifically tied to flat file connection managers. In general, if you are having problems with numeric values, bringing them in a strings so you can troubleshoot the values is helpful. As pointed out, the flat file source itself can handle null numeric values.

    Monday, June 25, 2007 9:01 PM
  • Right.

     

    I got thrown for a loop because my understanding of what you said seemed to contradict all the work I've done to this point, and that was freaking me out.

     

    But again, every wrong path I've taken has always helped me gain a more solid understanding of what I'm doing.

     

    It seems to me that unless you're having problems numeric values with a data file, best to just leave them alone, me thinks, as opposed to converting every value to a string first......... argh that seems like a nightmarish task.

     

    :-)

    Monday, June 25, 2007 9:38 PM