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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy