The SSIS FLOOR function is a mathematical function that returns the largest integer value that is less than or equal to the specified number or numeric expression. This article explains how to use the FLOOR function with an example and the syntax for returning the nearest integer less than or equal to the given value is as shown below.
FLOOR(<<numeric_expression>>)
To demonstrate the FLOOR function, we use the Product table below, which has 14 records. Please refer to the Derived Column Transformation, Union All Transformation, Built-in Functions, and SQL Server articles in SSIS.
SSIS FLOOR function
For this FLOOR example, let me 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 FLOOR expressions and create new columns.
Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS FLOOR function to the row cell under the Expression and replace the numeric_expression with the StandardCost column to return the small integer value that is less than or equals the existing one. Either you can manually delete the complete <<numeric_expression>> expression or drag and drop the StandardCost column will automatically replace it.
The below SSIS FLOOR function expressions return the largest integer value that is less than or equal to the product standard cost, Sales Amount, and Tax amount. The last two expressions show the performance of the FLOOR function on both positive and negative values.
FLOOR( [StandardCost] )
FLOOR( [SalesAmount] )
FLOOR( [TaxAmount] )
FLOOR( [Grade] )
FLOOR( [SalesAmount] - [StandardCost] )
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 FLOOR function package to see the FLOOR values of the standard cost, Sales amount, tax amount, and grade columns.
