###### By: Daniel Calbimonte | Last Updated: 2019-03-13 | Comments | Integration Services Development

##### Problem

I read the previous article about SQL Server Integration Services (SSIS) Expressions and was wondering what other things can be done with SSIS Expressions?

##### Solution

In this article we will show more examples of SSIS Expressions. We will use SQL Server 2017 and SSDT for Visual Studio 2015. However, most of the expressions are applicable in any SQL Server version.

## SSIS Expression to handle string with double quotes (")

If you have quotation marks in a string, you need to escape the character as follows:

Instead of having the string like this:

"this is "the best""

You need to escape the character (with a backward slash) as follows:

"this is \"the best\""

The output will be this:

this is "the best"

## SSIS Expression to concatenate strings to build email address

Using a Derived Column is the key to create the expression to combine strings to build an email address from a database table. For our example we have data in a table that has firstname and lastname and we want to concatenate those with the domain name.

You will need an OLE DB Source to connect to the SQL Server table with the firstname and lastname. Then we use a Derived Column task and then output the data to an OLE DB Destination to store the email in a table.

We will connect to the AdventureWorks2016 database and the Person.Person table that contains firstname and lastname:

In the Derived Column Task, we will create a derived column named **email**
with the following expression:

FirstName + "." + LastName + "@mssqltips.com"

Finally, you need the destination table to store the derived column values:

## SSIS Expression to generate random number

In SSIS there is not a default function to generate random values (tip 1 and tip 2). Usually, the script task is used with some functions.

The following example shows how to generate random numbers using linear congruential generator. This is an algorithm is used to create randomized numbers with a discontinuous linear equation:

X_{n+1}=(a*X_{n}+c) mod m

In the SSIS variables, you will need the **init** variable used
by the For Loop Container as shown below:

In addition, we need to create a **seed** variable. The seed is
the value where we want to start, in this example, we will start with 4.

In SSIS, we will generate multiple random values using the For Loop Container to generate multiple values in the expression:

In this example, we will use the For Loop Container to loop 20 times to generate
20 random values using the **init** variable:

In Expressions, we will create an expression based on the linear congruential numerator as follows:

@[User::seed] = (5*@[User::seed] +7) % 1000

The expression will generate different random numbers.

## SSIS Expression to parse filename

The token function is useful to get part of a string.

In the following example, we have a file called "file.txt". The token finds the first occurrence of the value "." and this will return the value to the left of the period.

TOKEN("file.txt",".",1)

The following example shows how to get the file extension, which is the second occurrence after the period.

TOKEN("file.txt",".",2)

## SSIS Expression Comment

There is no direct way to create comments, however you could use SSIS Annotation.

## SSIS Expression Case Statements

The case statements or switch cases are used to run one of several statements. They are common in programming languages.

For example, we want to categorize how old is a person according to their age. This is the pseudo-code:

Case When age >=18 then "Adult" When age < 18 and age and age> 1 then "Child" Else "Baby" End Case

- If age is equal or greater than 18 then Adult.
- If age is less than 18 and greater than 1, then Child.
- Else Baby.

This in the SSIS expression. It is kind of ugly, the way this needs to be written:

@[User::age]>=18 ? "Adult":@[User::age]<18 && @[User::age]>1 ? "Child":"Baby"

## SSIS Expression to Round Values

SSIS expressions include the ROUND function.

For example, if we have the number 5.5559 and we want to round with 2 decimals, the number will be rounded to 5.56.

ROUND((DT_DECIMAL, 4) 5.5559,2)

If you have the number 5.5549, the number rounded with 2 decimals will be 5.55:

ROUND((DT_DECIMAL, 4) 5.5549,2)

## SSIS Expression Inclusive OR and Exclusive XOR

This is the difference:

- Exclusive Or (XOR) is only true when condition x or y is true.
- Inclusive Or (OR) is true when condition x or y is true or both are true.

The Inclusive Or (OR) works as follows:

x | y | result |
---|---|---|

0 | 0 | 0 |

0 | 1 | 1 |

1 | 0 | 1 |

1 | 1 | 1 |

The Exclusive Or (XOR) works like this:

x | y | result |
---|---|---|

0 | 0 | 0 |

0 | 1 | 1 |

1 | 0 | 1 |

1 | 1 | 0 |

They are similar, except for the last row of the table.

Let’s check an example with (OR):

3 | 5

We convert 3 into a binary number (011) and 5 to a binary number (101) (check the next steps for online decimal to binary converters).

The result of this expression is 7 (111).

If we compare the positions 011 to 101 using the **OR** table above,
they are all true and therefore return 111 or 7.

Decimal | Binary |
---|---|

3 | 011 |

5 | 101 |

7 | 111 |

If we try with XOR:

3 ^ 5

We convert 3 into a binary number (011) and 5 to a binary number (101).

The result of this expression is 6 (110).

If we compare the positions 011 to 101 using the **XOR** table above,
position 1 and 2 are true, but position three is not so we get 110 or 6.

Decimal | Binary |
---|---|

3 | 011 |

5 | 101 |

6 | 110 |

## SSIS Expression to Square Number

In SSIS 3^3 is not 9. It is 0 as shown above.

If you want the square, you should use the square function like this:

square(3)

## SSIS Expression for Square Root

For the square root, this requires the sqrt function.

sqrt(9)

The square root of 9 is 3.

## Conclusion

In this article, we saw different examples of functions used in SSIS expressions. We included some functions, case expressions, tokens, random values and more.

##### Next Steps

For more information about this exam, refer to these links:

- ^ (Bitwise Exclusive OR) (SSIS Expression)
- | (Bitwise Inclusive OR) (SSIS Expression)
- ? : (Conditional) (SSIS Expression)
- TOKEN (SSIS Expression)

Last Updated: 2019-03-13

##### About the author

**View all my tips**