April 2, 2015 at 11:41 am
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
April 2, 2015 at 12:04 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply