The SSIS ROUND function is a mathematical function that returns the integer value that is nearest or close to the specified number or numeric expression. This article explains how to use the ROUND function with an example and the syntax for returning the closest or rounded integer to the given value is as shown below.
ROUND(<<numeric_expression>>, <<length>>)
The length parameter accepts integers only. It decides how many decimal values the result has to keep. For instance, 0 means the value rounded to an integer without any decimals. To demonstrate the SSIS ROUND function, we use the Product table below, which has 14 records.
SSIS ROUND function
For this rounded values example, add a Data Flow Task and double-click on it to enter the Data Flow region. Next, add the OLE DB Source to read data from the above SQL database product table. Please join the OLE DB Source to Derived Column Transformation because it helps to write ROUND expressions and create new columns.
Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS ROUND function to the row cell under the Expression and replace the numeric_expression with the SalesAmount to return the rounded sales values. Either you can manually delete the complete <<numeric_expression>> expression or drag and drop the SalesAmount column will automatically replace it.
The below SSIS ROUND function expressions use Sales as the first argument and 0 to 4 integers as the length. 0 means Sales rounded to the nearest integer without decimals and 4 means Sales rounded to the nearest integer with four decimals. The rounded value will be the 4th decimal point.
ROUND( [SalesAmount], 0 )
ROUND( [SalesAmount], 1 )
ROUND( [SalesAmount], 2 )
ROUND( [SalesAmount], 3 )
ROUND( [SalesAmount], 4 )
Similarly, the below expression rounds the Grade column to no decimals and two decimals precision. Please refer to the Derived Column Transformation, Union All Transformation, Built-in Functions, and SQL Server articles in SSIS.
ROUND( [Grade], 0 )
ROUND( [Grade], 2 )
Click OK to close the Editor window.
Drag and drop the Union All Transformation and connect the Derived Column to it. Next, right-click on the connection node and choose the Enable Data Viewer. Please run the SSIS ROUND function package to see the rounded values of the Sales amount and grade columns.
