Using SQL Server Integration Services, you can perform all kinds of operations on your company’s data. Through a user-friendly environment with a relatively easy learning curve, you can create a vast range of complex and scalable data-rich solutions. Moreover, all of this is done within an easy-to-use IDE, where coding is often unnecessary for most common tasks.
Fitting naturally into Microsoft environments, SSIS is also capable of connecting with diverse data sources and destinations. These integration services seamlessly work for highly heterogeneous solutions. For instance, you can load data from flat files, Excel, or other databases into the SQL Server database and then set an output for equally diverse destinations.
The scalability potential of SSIS makes this connectivity solution a perfect choice for third-party development and flexible integration into the original ecosystem. This flexibility stems mainly from the ability to use SSIS expressions, which you can write to return specific values, calculate values on the fly while executing the SSIS package, and perform other advanced data manipulations. As a result, the execution of the data flow will be directed dynamically according to the incoming data or environment variables.
In this article, we’ll overview SSIS expressions, which are commonly used to parametrize the connection strings. We’ll also provide a list of operators and functions you can use to streamline your SSIS connectivity, and show how you can use SSIS expressions syntax for a Data Flow.
Table of Contents
- SSIS Expression Syntax
- SSIS Examples
- How to Use SSIS Expressions
- How Devart Components Extend the Capabilities of Standard SSIS Tasks
- Conclusion
SSIS Expression Syntax
An expression is a user-defined code snippet that returns a value. Expressions often include functions, operators, identifiers, symbols, and literals that are then used as parameters for a connection.
Let’s overview some of the most common syntax elements you can use to write SSIS expressions.
Operators
You can use these operators to define what action you want to perform with the data. Check the list below to ensure you are aware of all the options.
| Operator | Description |
|---|---|
| + | Adds two numeric expressions. |
| + | Concatenates two expressions. |
| – | Subtracts the second numeric expression from the first one. |
| – | Negates a numeric expression. |
| * | Multiplies two numeric expressions. |
| / | Divides the first numeric expression by the second one. |
| % | Provides the integer remainder after dividing the first numeric expression by the second one. |
| () | Identifies the evaluation order of expressions. |
| == | Performs a comparison to determine if two expressions are equal. |
| != | Performs a comparison to determine if two expressions are not equal. |
| > | Performs a comparison to determine if the first expression is greater than the second one. |
| < | Performs a comparison to determine if the first expression is less than the second one. |
| >= | Performs a comparison to determine if the first expression is greater than or equal to the second one. |
| <= | Performs a comparison to determine if the first expression is less than or equal to the second one. |
| && | Performs a logical AND operation. |
| || | Performs a logical OR operation. |
| ? | Returns one of two expressions based on the evaluation of a Boolean expression. |
| & | Performs a bitwise AND operation of two integer values. |
| | | Performs a bitwise OR operation of two integer values. |
| ^ | Performs a bitwise exclusive OR operation of two integer values. |
| ~ | Performs a bitwise negation of an integer. |
| ! | Negates a Boolean operand. |
Functions
There is a wide range of functions available in SSIS, separated into different types depending on the type of data they handle. Familiarize yourself with the most common ones you can use to build an expression.
Math functions
| Type: Math | Function | Parameters | Description |
|---|---|---|---|
| Math | ABS | (NumericExpression) | Returns the absolute, positive value of a numeric expression. |
| Math | CEILING | (NumericExpression) | Returns the smallest integer greater than or equal to a numeric expression. |
| Math | EXP | (NumericExpression) | Returns the exponential of the specified expression. |
| Math | FLOOR | (NumericExpression) | Returns the largest integer that is less than or equal to a numeric expression. |
| Math | LN | (NumericExpression) | Returns the natural logarithm of a numeric expression. |
| Math | LOG | (NumericExpression) | Returns the base-10 logarithm of a numeric expression. |
| Math | POWER | (NumericExpression, Power) | Returns the result of raising a numeric expression to a power. The power parameter must be evaluated to an integer. |
| Math | ROUND | (NumericExpression, Length) | Returns the integer that is closest to a given value. The length parameter must be evaluated to an integer. |
| Math | SIGN | (NumericExpression) | Returns the positive (+1), negative (-1), or zero (0) sign of a numeric expression. |
| Math | SQUARE | (NumericExpression) | Returns the square of a numeric expression. |
| Math | SQRT | (NumericExpression) | Returns the square root of a numeric expression. |
String functions
| Type: String | Function | Parameters | Description |
|---|---|---|---|
| String | CODEPOINT | (Character expression) | Returns the Unicode code value of the leftmost character of a character expression. |
| String | FINDSTRING | (Character expression, String, Occurrence) | Returns the location of the specified occurrence of a string within a character expression. |
| String | HEX | (Character expression) | Returns a string representing the hexadecimal value of an integer. |
| String | LEFT | (Character expression, number) | Returns the left part of a character expression with the specified number of characters. |
| String | LEN | (Character expression) | Returns the number of characters in a character expression. |
| String | LOWER | (Character expression) | Returns a character expression after converting uppercase characters to lowercase characters. |
| String | LTRIM | (Character expression) | Returns a character expression after removing leading spaces. |
| String | REPLACE | (Character expression, Search Expression, Replace Expression) | Returns a character expression after replacing a character string within the expression with either a different character string or an empty string. |
| String | REPLICATE | (Character expression, times) | Returns a character expression that is replicated a number of times. |
| String | REVERSE | (Character expression) | Returns a character expression in reverse order. |
| String | RIGHT | (Character expression, number) | Returns the right part of a character expression with the specified number of characters. |
| String | RTRIM | (Character expression) | Returns a character expression after removing trailing spaces. |
| String | SUBSTRING | (Character expression, Start, Length) | Returns the part of a character expression that starts at the specified position and has the specified length. |
| String | TOKEN | (Character expression, Delimiter Expression, Occurrence) | Returns the specified occurrence of a token in a string. A token may be marked by a delimiter in a specified set of delimiters. Returns an empty string if not found. |
| String | TOKEN COUNT | (Character expression, Delimiter Expression) | Returns the number of tokens in a string. A token may be marked by a delimiter in a specified set of delimiters. |
| String | TRIM | (Character expression) | Returns a character expression after removing leading and trailing blanks. |
| String | UPPER | (Character expression) | Returns a character expression after converting lowercase characters to uppercase characters. |
Time functions
| Type: Time | Function | Parameters | Description |
|---|---|---|---|
| Time | DATEADD | (DatePart, Number, Date) | Returns a new DT_DBTIMESTAMP value after adding a number that represents a date or time interval to the specified datepart in a date. |
| Time | DATEDIFF | (DatePart, StartDate, EndDate) | Returns the number of date and time boundaries crossed between two specified dates. The datepart parameter identifies which date and time boundaries to compare. |
| Time | DATEPART | (DatePart, Date) | Returns an integer representing a datepart of a date. |
| Time | DAY | (Date) | Returns an integer that represents the day datepart of a date. |
| Time | GETDATE | Returns the current date of the system. | |
| Time | GETUTCDATE | Returns the current date of the system in UTC time (Universal Time Coordinate or Greenwich Mean Time). | |
| Time | MONTH | (Date) | Returns an integer that represents the month datepart of a date. |
| Time | YEAR | (Date) | Returns an integer that represents the year datepart of a date. |
NULL functions
| Type: NULL | Function | Parameters | Description |
|---|---|---|---|
| NULL | ISNULL | (Expression) | Returns a Boolean result based on whether an expression is null. |
| NULL | REPLACENULL | (Expression, Expression) | Returns the value of the second expression parameter if the first expression parameter is null. |
| NULL | NULL | (DataType) | Returns the value for a NULL for the data type passed as parameter. |
Type casts
| Type casts | Function | Parameters | Description |
|---|---|---|---|
| Type Casts | (DataType) | Expression to convert | Returns the casted type passed as a parameter. You can convert from to any data type supported by IIS to properly manage the data types inside a solution. |
SSIS Examples
To better understand how SSIS expressions are built, here are several examples to explore in detail.
Concatenate FirstName and LastName to obtain the complete name
Since we have two separate strings and an operator that concatenates the values, we can use it to get the desired output.
Expression: [First Name] + ” ” + [Last Name]
FirstName: “Giancarlo”
LastName: “Milano” Output: “Giancarlo Milano”
Check if the LastName is empty, returning False or True
In this example, we determine if two expressions are equal and look for empty expressions. Since the LastName isn’t empty, we get False output.
Expression: [Last Name] == “”
LastName: “Milano”
Output: False
If the LastName is empty, the output will return True.
Expression: [Last Name] == “”
LastName: “”
Output: True
Check if the Age value is over 18 years by returning True
For age validation, you can easily use the following comparison operator.
Expression: Age >= “18”
LastName: 35
Output: True
Check if the Age value is between 18 and 28 years
Alternatively, you can check if the value falls in a certain age range.
Expression: Age >= “18” && Age <= "28"
LastName: 25
Output: True
These are only several examples of using SSIS expressions to perform operations on the data you pull. Note that there are more operators you can explore yourself using the same syntax.
How to Use SSIS Expressions
Let’s create a practical example using several expressions we’ve already discussed to understand how to apply them in real-world scenarios and SSIS tasks. Follow the guide below to see how the expressions are used.
- Create a new SSIS Project and add a Sequence Container. Within the Sequence Container, place a Data Flow Task. This will establish the fundamental environment for trying our examples in action.
- Open the Data Flow Task and construct the following layout: a Data Source, a Data Output, and a Derived Column element between them to test SSIS Expressions. You can use a simple file input and output to streamline the demonstration, as shown in the picture below, but any other options are also valid.
- Here’s a prepared dataset to serve as input.
Expression: Age >= “18” && Age <= "28"
LastName: 25
Output: True
Double-click the Derived Column to enter it.
- Let’s create some expressions to get different results from the source data. Highlight the columns that you want to modify. The rest will remain as the default values suggested by SSIS.
| Derived Column Name | Expression |
|---|---|
| FullName | [First Name] + ” ” + [Last Name] |
| [Last Name] + [First Name] + “_” + Country + “@” + “school.org” | |
| Age | DATEDIFF(“yy”,(DT_DBTIMESTAMP)BirthDate,GETDATE()) |
| IsOver18Years | DATEDIFF(“yy”,(DT_DBTIMESTAMP)BirthDate,GETDATE()) > 18 |
- Press OK to complete the configuration:
- The final package will generate a flat file output with the data generated based on the expressions you have created.
How Devart Components Extend the Capabilities of Standard SSIS Tasks
Clearly, using SSIS expressions can save you so much time, as you do not have to pull all the data, accelerating integration. However, there are even more options to facilitate the process.
Check the SSIS Data Flow Components from Devart to simplify your work with sources and destinations, as well as perform a lookup that won’t require you to actually pull the data to see what results you’ll be getting, significantly reducing the server round-trips.
Try SSIS Data Flow Components from Devart for accelerated integration with cloud applications and databases!
Conclusion
With SSIS expressions, you can easily handle many possible operations with data. Certain scenarios and tools exist where writing a simple expression can help you avoid complex constructions and data manipulations, reduce the workload, and accelerate operations.
Moreover, you can also use SSIS expressions to automate various tasks: generate new data from existing data, perform data validation, and direct data flow based on the result of expressions.
Combined with the SSIS Data Flow Components, this can significantly enhance your data integration speed.
