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:
Xn+1 =(a*Xn+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:
