SSIS Operators

The SSIS or SQL Server Integration Services supports all the important operators such as Arithmetic, Comparison, Logical, and Bitwise operators to perform a wide variety of operations. In real-time, you can use them alone or multiple combinations to achieve the goals. This SSIS article gives the description of each operator and a detailed example using the derived column to understand the real-time scenarios.

To demonstrate the SSIS operators, we use the Product table below, which has 14 records. Please refer to the Union All Transformation, Derived Column Transformation, Built-in Functions, and SQL Server articles in SSIS.

Source Table

SSIS Operators Example

For these operators’ examples, 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 table. Please join the OLE DB Source to Derived Column Transformation to write operator expressions and create new columns.

Data Flow to add Derived Column

Double-click on it to open the Derived Column Transformation Editor. Next, drag and drop the required column name to the row cell under the Expression and use the appropriate operator to perform the operations.

SSIS Arithmetic Operators

The following is the list of available SSIS arithmetic operators to perform addition, subtraction, multiply, divide, remainder, and negate.

  1. + (Add): It adds two numeric expressions.
  2. + (Concatenate): If you use it on string columns, it concatenates two expressions. For example, to get the Full name, you can use + for string concatenation [FirstName] + [LastName].
  3. – (Subtract): It Subtracts the second value from the first numeric expression.
  4. – (Negate): It Negates a numeric expression.
  5. * (Multiply): It Performs the Multiplication of two numeric expressions.
  6. / (Divide): It divides the first number by the second numeric expression.
  7. % (Modulo): It returns the reminder value after dividing one number by another.
  8. () (Parentheses): It helps to identify the calculation priority (evaluation order).

The below expressions show the utilization of SSIS arithmetic operators on the table columns.

Add  =  [SalesAmount] + [TaxAmt]

Subtract = [SalesAmount] - [StandardCost]

Multiply = [StandardCost] * [Orders]

Negate = - [SalesAmount]

Divide = [SalesAmount] / [Orders]

Modulo = (DT_I8)[SalesAmount] % 4

No Parentheses = [StandardCost] - [SalesAmount] + [TaxAmt] * [Orders]

Parentheses = (([StandardCost] - [SalesAmount]) + [TaxAmt]) * [Orders]
SSIS Arithmetic Operators expression

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 Arithmetic Operators package to see the arithmetic operations.

SSIS Arithmetic Operators add, subtract, multiply, divide, modulo Output

SSIS Comparison Operators

The following is the list of available SSIS comparison operators to check equal, not equal, greater than, less than, greater than or equal, and less than or equal.

  • == (Equal) It compares the two expressions and checks whether both are equal or not.
  • != (Unequal) It compares the two expressions and checks whether both are not equal.
  • > (Greater than) It checks whether the first expression is greater than the second.
  • < (Less than) It checks whether the first expression is less than the second.
  • >= (Greater than or Equal to) It checks whether the first expression is greater than or equal to the second.
  • <= (Less than or Equal to) It checks whether the first expression is less than or equal to the second.

The below expressions show the utilization of SSIS comparison operators on the table columns.

[Color] == "Black"

[SalesAmount] > [StandardCost]

[SalesAmount] < [StandardCost]

[StandardCost] >= 413.1463

[Grade] <= 0
SSIS Comparison Operators expression

As you know, the comparison operators check the expression and return the result as a boolean True or False. So, you can use the conditional operator to return the message or information.

If you use the SSIS comparison operators inside the conditional split transformation, you can divert the incoming rows to multiple destinations. For instance, if you use the above expression inside the conditional split, you get five different result sets.

SSIS Comparison Operators expression inside conditional split

Click OK to close the Editor window. Next, run the SSIS comparison Operators package to see the comparison operations.

SSIS comparison Operators result

SSIS Logical and Bitwise Operators

The following is the list of available SSIS Logical and Bitwise operators such as AND, OR, NOT, XOR, and Conditional.

  1. && (Logical AND) It performs a logical conjunction AND operation.
  2. || (Logical OR) It performs a logical disjunction OR operation.
  3. ? : (Conditional) It checks the given expression and based on the result, it returns one of the two expressions.
  4. & (Bitwise AND) It performs a Bitwise AND operation on two integer values.
  5. | (Bitwise Inclusive OR) It performs a Bitwise OR operation on two integer values.
  6. ^ (Bitwise Exclusive OR) It performs a Bitwise exclusive OR operation on two integer values.
  7. ~ (Bitwise Not) It performs a bitwise negation of an integer.
  8. !(Logical Not) Negates a boolean operand.

Similar to the comparison, the logical operators return the boolean True or False. So, use the Conditional operator to show the message. The below expressions show the utilization of SSIS logic and bitwise operators on the table columns.

[SalesAmount] > 1000 && [Orders] >= 6

[SalesAmount] > 1000 || [Orders] >= 6

[Orders] != 2

9 & 65

9 | 65

9 ^ 65

~9

([SalesAmount] > [StandardCost]) ? "Good" : "Bad"
SSIS logical and bitwise Operators Expression

Click OK to close the Editor window. Next, run the SSIS logical and bitwise Operators package to see the operations. The logical operations. Check both true and false as explained above. The Bitwise operators operate at the bit level. For instance, 9 = 0001001 and 65 = 1000001.

  • 0001001 & 1000001 = 0000001 = 1
  • 0001001 | 1000001 = 1001001 = 73
  • 0001001 ^ 1000001 = 1001000 = 72
SSIS logical and bitwise AND,  OR, NOT Operators package output