Ugh. I have been fighting rounding problems in my SSIS package since yesterday morning. I have traced the rounding problem back to what appears to be an undesired rounding of a decimal to the tenths place when retrieving data via OLE DB Source and I can't figure out how to fix that. I have a Source database which is Sybase. There is a table (let's call it "foo"). In the "foo" table there is a column (let's call it "amount"). For a given row in the "foo" table, the "amount" column equals exactly 1.875. When my OLE DB source retrieves the data it retrieves the value of "amount" for that row as 1.9 instead of 1.875. If I use an MS Access app to connect to that same Sybase database via ODBC and retrieve that exact same row from the "foo" table the value of "amount" is correctly retreived as 1.875. I have tried playing with the advanced settings of my OLE DB source and setting the source column type to double precision float instead of just float (DT_R8 vs DT_R4) to no success. When I preview the data (or run the SSIS package) the value is retreived as 1.9 instead of 1.875. Any help on why this is happening would be appreciated. It is causing me a real headache (and inaccurate data results as well) - Charles Charles

Shouldn't you use DECIMAL? DT_DECIMAL An exact numeric value with a fixed precision and a fixed scale. This data type is a 12-byte unsigned integer with a separate sign, a scale of 0 to 28, and a maximum precision of 29. Arthur My Blog

Thanks Arthur I will give that a try and get back to you. The problem I was having earlier is the OLE DB source would not allow me to change the data type. it sees it as DT_R4 and won't seem to let me change that. I can't change the data type of the source database... I will give it another try in the advanced properties of SSIS...Charles

Arthur, what I am doing wrong...? In the OLE DB Source the only way I can figure to tell SSIS to treat the "amount" column as a decimal is to change it to DT_DECIMAL under the Advanced Editor -> Input and Output Properties. If I tell it the source is DT_DECIMAL it gives me an error saying that the error output must be changed to match otherwise it is in an "invalid state" but when I try to change the column under the error output to be DT_DECIMAL (or anything other than float DT_R4) it gives an error saying "...The data type for "output "OLE DB Source Error Output" (12)" cannot be modified in the error "output column "amount" (60)". Error at [OLE DB Source]: Failed to set property "DataType" on "output column "amount" (60)"." - CharlesCharles

Arthur, what I am doing wrong...? In the OLE DB Source the only way I can figure to tell SSIS to treat the "amount" column as a decimal is to change it to DT_DECIMAL under the Advanced Editor -> Input and Output Properties. If I tell it the source is DT_DECIMAL it gives me an error saying that the error output must be changed to match otherwise it is in an "invalid state" but when I try to change the column under the error output to be DT_DECIMAL (or anything other than float DT_R4) it gives an error saying "...The data type for "output "OLE DB Source Error Output" (12)" cannot be modified in the error "output column "amount" (60)". Error at [OLE DB Source]: Failed to set property "DataType" on "output column "amount" (60)"." - Charles Charles Arthur - It didn't work. Here is what I did... On my OLE DB Source using the Advanced Editor / Input and Output Columns -> I changed the data type of the OLE DB Source Output - Output Columns to DT_DECIMAL. I cannot change the "External Columns" data type or the OLE DB Source Error Output for the reasons I described in my last post. I can only change the OLE DB Source Output - Output Columns data type. When I do this the rounding error is now significantly magnified (worse). When you suggested using DT_DECIMAL is this what you meant? If not maybe I missing something... Thanks in advance, - Charles Charles

Have you tried using Sybase ODBC provider under SSIS and ADO.NET DataReader component?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Have you tried using Sybase ODBC provider under SSIS and ADO.NET DataReader component? SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ Interesting thought thanks CozyRoc. Right now I am pretty much stuck with OLE DB drivers but I did wonder if the driver might be the issue. I have built another test DFT to try and isolate the problem using the current OLE DB driver and am not yet convinced the driver is the issue... Thanks for the comment.Charles

Charles, I suspect the driver is the issue. If you have the ODBC drivers installed, you should be able to setup a test in about 5 minutes.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Hello SSIS Forum - any other suggestions on this appreciated. I am still dealing with this rounding problem. I don't think it's the driver. I have tried ODBC and get the same results while my Access app which uses the exact same driver has no rounding problems. I am able to retrieve and insert a decimal with many digits to the right of the decimal from Sybase and insert it directly into SQL Server. However if I have a derived column that multiplies that number and stores the result that is where the rounding error occurs. I have tried DT_R4, DT_R8 (Float) and Numeric data types with the precision set to 18 and scale set as high as 6 and the best I get in the result is a single digit of precision to the right of the decimal. Please help this is really killing me!Charles

Hello here is an update on my rounding woes... It appears that the driver is not the problem. OLE DB vs ODBC makes no difference. Also my access app uses the same drivers and does not exhibit the same behavior, the problem only occurs in SSIS. I have run a series of tests and found that: In a derived column if I simply retrieve the value of an existing numeric / decimal column and store that in a new column the full precision is passed through. For example if I have existing Column "A" which has a value of 4.56789 and I derive a new column "B" and say that B = A then when B is saved to my SQL Server 2005 database in a float column and it later retrieved the value is 4.56789 just as it should be - great. If on the other hand I create a derived column and I multiply by 25, i.e. Col B = (Col A * 25) then in the multiplied result the resulting value is rounded to the tenth's place. If I add 25 instead of multiply the rounding / truncation does not occur. If I multiply by a small number like 1 or 2 the truncation also does not occur. While the rounding / truncation problem does not seem to occur when adding, I have noticed that the decimal values often change when they should not. For instance if Col A = 4.56789 and I set derived column B = (A + 25) then the resulting value of B may be 29.4523132 (the "29" part is correct but how did .56789 become something else when only a whole number was added? Very strange.. Still working on this... - Charles Charles

Charles, Can you try to retrieve data with such precision from SQL Server and doing the same numeric operations just like what you have described above. I think you are onto something and it is not even related to Sybase. It looks like there might be rounding bug in the SSIS numeric expressions.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

CozyRoc, Yes I can do that. I will give it a try and report back. It may be tomorrow morning before I post the results... Thanks for the test idea.... - CharlesCharles

CozyRoc, Well I stubbed out a test this morning doing the exact same thing with SQL Server as the Source vs Sybase as you suggested. The problem does NOT occur with SQL Server as the source... there is NO unwanted rounding in the calculation, the full precision is presented. So maybe this is somehow Sybase related or related to the interaction between Sybase and SSIS. Funny thing is that it does not happen with Access working with Sybase or with SSIS working with SQL Server... strange. Maybe I will have to find a way around using the Derived Column for multiplication when Sybase is the source of the data...Charles

SOLVED!!! In deference to my last post, while I did a small stubbed out test with SQL Server as the data source instead of Sybase and that seemed to work... when I moved my actual solution to use this approach I got the same exact rounding / truncation problem. I truly think this may be a bug in SSIS (that or I am doing something really obviously wrong and just don't see it). Anyway I have now solved the problem by doing the calculations in SQL instead of in an SSIS derived column. I am also still using a staging table to I can avoid any potential Sybase issues (my solution actually uses both Sybase and SQL Anywhere as the source so it is better for consistency to have all the calculations done from SQL Server hence the staging table). So here are some key points and what now works for me (I no longer have any unwanted rounding problems)... I am using SSIS 2005 / SQL Server 2005 The problem occurs when trying to multiply two values in an SSIS Derived Column and these two values are columns from a database, at least one of which has high-precision (lots of numbers) to the right of the decimal. An example would be 52.359234934 * 25. The problem I was having is that the result would be rounded to the tenth's place and I wanted full precision. The assigned data type of the derived column made no difference nor did the data type of the columns from which the data was pulled. I had this problem regardless of the source database (Sybase, SQL Anywhere, MS SQL Server) and the drivers did not seem to matter (ODBC, OLE DB, etc..) I verified the problem did not occur if I did the exact same thing using an MS Access 2003 app using the same databases and drivers. Thus I think this is an SSIS bug. Simply moving a high-precision value from one database to another was not a problem. To solve the problem I first take the data to be calculated and in one data flow I move it from Sybase to a staging table in my SQL Server 2005 database. Then in a separate data flow I retrieve the data from the staging table and do the calculations in SQL (i.e. SELECT column_a * column_b AS result). Whatever other logic I may have that uses IF THEN type logic (in SSIS using [True/False Statement] ? [If True] : [If False] type format) I still do in a derived column but any actual calculations involving multiplication I do in advance in the SQL itself thereby bypassing what I believe is an SSIS bug. I hope this info is helpful to others. This problem certainly caused me a lot of headache and time... - Charles Charles

This surely looks like a bug with precision in the SSIS expressions. I have specified as expression to a variable of type double: (DT_R8)52.359234934 * (DT_R8)25 When I evaluate it, I get as result: 1308.98087335 However if I specify: (DT_R8)52.359234934 + (DT_R8)25 And evaluate it, I get as result: 77.359234934 So clearly there is an issue in the multiplication of high precision values. This looks like real bug. 135AVIATOR: I have a request? Can you file this bug in the SQL Server Connect System here ? ----------------------------------------------------------------- Anyone from MS who can comment on this ?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Hi CozyRoc I can definitely make a bug report because I think it is a bug (thanks for the link) but in the calculations you did and posted these actually look correct to me... The result of 52.359234934 * 25 is and should be 1308.98087335 and 52.359234934 + 25 is and should be 77.359234934. Did you mistype or did I mis-read your last response? In my experimentation, if I just typed the values into the derived column (ie created a column set = to 52.359234934 * 25 then the result would come out right. The problem only seems to occur when at least one of the two values being multiplied are retrieved from a database... - Charles Charles

Hi CozyRoc I can definitely make a bug report because I think it is a bug (thanks for the link) but in the calculations you did and posted these actually look correct to me... The result of 52.359234934 * 25 is and should be 1308.98087335 and 52.359234934 + 25 is and should be 77.359234934. Did you mistype or did I mis-read your last response? In my experimentation, if I just typed the values into the derived column (ie created a column set = to 52.359234934 * 25 then the result would come out right. The problem only seems to occur when at least one of the two values being multiplied are retrieved from a database... - Charles Charles Charles, Sorry , my mistake. You are correct. You'd better describe your test case in the bug report and explain that the source database is SQL Server.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Hi CozyRoc I can definitely make a bug report because I think it is a bug (thanks for the link) but in the calculations you did and posted these actually look correct to me... The result of 52.359234934 * 25 is and should be 1308.98087335 and 52.359234934 + 25 is and should be 77.359234934. Did you mistype or did I mis-read your last response? In my experimentation, if I just typed the values into the derived column (ie created a column set = to 52.359234934 * 25 then the result would come out right. The problem only seems to occur when at least one of the two values being multiplied are retrieved from a database... - Charles Charles Charles, Sorry , my mistake. You are correct. You'd better describe your test case in the bug report and explain that the source database is SQL Server. SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ OK no problem I will submit the bug report based on the tests I ran. Thanks again for all your help. - Charles Charles

The bug has been reported to Microsoft at the link provided by CozyRoc. The bug # is 617173 and is marked for public viewing. https://connect.microsoft.com/SQLServer/feedback/details/617173/ssis-2005-derived-column-rounding-truncation-error-multiplying-high-precision-numbers# - CharlesCharles

The bug has been reported to Microsoft at the link provided by CozyRoc. The bug # is 617173 and is marked for public viewing. https://connect.microsoft.com/SQLServer/feedback/details/617173/ssis-2005-derived-column-rounding-truncation-error-multiplying-high-precision-numbers# - Charles Charles Perfect report. Thank you, Charles!SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

My pleasure. - Charles :)Charles