Rounding issues with OLE DB Destination in SSIS

Problem:  Your MS Excel source data holds a value, 12.3456789.  You need to INSERT this value into a SQL Server destination table with a column of type decimal(19,2).  The desired result is 12.35, however when you use the SSIS Data Flow Task with the OLE DB source and destination components, your result is 12.34.  By default, the underlying code in the SSIS task will truncate the remaining decimal places rather than using a rounding function.

Troubleshooting:  To keep things simple, I decided to use a SQL Server Table source instead of an Excel source for this example (the core issue remains the same and we’ll assume that we cannot change the way we query the source data; for example, “SELECT ROUND([Amount], 2) FROM <table or worksheet>…”).  So, first, let’s create two tables.  One table representing the source data:

CREATE TABLE [dbo].[tbl_Source]([Amount] [float] NULL)

INSERT INTO [dbo].[tbl_Source]([Amount]) VALUES (12.3456789)

…and one table representing the destination data:

CREATE TABLE [dbo].[tbl_Destination](

      [Test_ID] [int] IDENTITY(1,1) NOT NULL,

      [Amount] [decimal](19, 2) NOT NULL

)

Now let’s manually execute an INSERT statement:

INSERT INTO [dbo].[tbl_Destination]([Amount])

SELECT [Amount] FROM [dbo].[tbl_Source]

The results are (Test #1):

Somewhere behind the scenes, the SQL Server database engine is implicitly handling the rounding for us.  This is the behavior I was expecting out of my SSIS package:

…here are the result produced from the steps above (Test #2):

..12.34 is not what I was expecting!  I was running Profiler while the package above executed and here is the important statement:

insert bulk [dbo].[tbl_Destination]([Amount] decimal(19,2))with(TABLOCK,CHECK_CONSTRAINTS)

Who has used THAT statement before?  And where in the world is the source data coming from?  Apparently “INSERT BULK” is undocumented feature, so let’s try changing the “Data Access Mode” of the OLE DB Destination task from “Table or view – fast load” to just “Table or view”:

The results are the same (Test #3):

…however, the key statements that I gathered from Profiler are:

INSERT [dbo].[tbl_Destination]([Amount]) VALUES(@Param000004)

exec sp_cursor 180150003,4,0,N'[dbo].[tbl_Destination]',@Amount=12.34

So, the process was definitely different in the backend, but the results were the same.  I looked into the SQL Server Destination component but that would not implicitly convert the float to the decimal(19,2).  I also looked into the Data Conversion component, but it would not let me specify the precision parameter for the decimal data type.

Solution:  So then I decided to look into expressions.  The expressions on the existing Source and Destination Data Flow components would not do what I needed them to do.  The Derived Column component would:

…and the properties window for the Derived Column (Expression = ROUND(Amount, 2)):

…and the result (Test #4):

This wasn’t too hard to figure out and probably not worth blogging about, but I’m still new to SSIS, had some idle time and writing all this down helps me learn, so there you go!  Feel free to show me other ways to accomplish this or if you have any questions.