SSIS Data Loss error messages during CSV import to SQL

• Paul Stasny

  Mr or Mrs. 500

  Points: 599

  More actions

  April 2, 2015 at 11:41 am

  #74426

  I am trying to import a Csv with text, decimal values and dates stored as 2013092015 to SQL 2012 using SSIS and struggling with data types. For example, I have a field in the flat file connection Manager called Price defined as FLOAT (DT-94) - as suggested by IIS. It appears that way in the flat file Input & Output Properties for both external column and output columns & the OLE DB Destination.

  It is going into a SQL Column defined as (Real, Null). I used Real from a Site showing SSIS to SQL Server data type translations. I also tried decimal with 15 digit precision and a scale of 2

  The data conversion for Column Price has returned status value 2 . The value could not be converted because of a potential loss of data.

  It refers to a specific row with a value of 1400. There are no blank fields in the csv and all of the values look like 300, 688.5, 395.25

  I tried setting the truncation errors to ignore with no result. I even tried bringing this all in as string values into SQL Columns defined as Varchar(500) thinking I could deal with this once I get the csv values into SQL. At present, nothing is being imported.

  I need to get this data into a staging table and I will be updating or inserting rows into a Production table depending on whether these are new or modified orders.

  I would appreciate any suggestions to get by this. Thanks

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  April 2, 2015 at 12:04 pm

  #1788400

  The column data types in the flat file source should match the destination table. So the flat file source column should be changed to DT_R4, or the database column should be changed to FLOAT.

  If the input file really does have a value that doesn't fit a REAL (DT_R4), you'll get an error from the flat file source component rather than the destination.

  I wouldn't rule out something like a stray comma in the input file that is throwing it off. Try copying the error-generating line to a separate file, then run it through your package in BIDS with a data viewer on the output of the flat file source. Verify that the column in question has the value you think it should.