Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SSIS Expressions for Email Addresses, Random Numbers, File Names, Round, OR and XOR Logic and More Examples using SQL Server Integration Services


By:   |   Last Updated: 2019-03-13   |   Comments   |   Related Tips: More > 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.

SSIS derived column expression

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

OLEDB Source query

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

FirstName + "." + LastName + "@mssqltips.com"			
SSIS email expression

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

SSIS destination example

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:

SSIS integer variable

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.

seed and variables

 

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

Loop with random values

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

For loop configuration in SSIS

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

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

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:



Last Updated: 2019-03-13


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools