Script-expression in SSIs

An expression is a combination of elements to generate a unique value. These elements include variables, literal faces, functions, stored procedures, and operators. The expression of a short answer is 1 <2, and the result is true. Although the expressions are derived from the C language, they are case insensitive.

Many tasks in SSIs support expressions. All tasks support attribute configuration using expressions. For example, expressions can be used in for loop and foreach loop to set loop conditions. The derived Column task can use expressions to define output.

Dynamic attributes

In SSIs, you can use expressions to dynamically execute conversion tasks.
Right-click the task and select Edit. The Edit page is displayed. There are some category tags on the left. Click these tags to open a category-related attribute. Each task has an expression tag, 1

Figure 1

There are two columns in this editing box. One is the expression name and the other is the expression value. In the expression name, you can open a drop-down list and select the expression to be used in this table. Then, you can edit an expression value in the value column. When editing the expression value, you can also click the garden button on the right to open the expression value editor. 2

Figure 2

Click the plus sign next to the expression in the right sidebar to view all the expressions. Click the circle button next to the text box to open the expression editing box, 3

Figure 3

This editor is very powerful. The tree structure in the left border lists the expressions to be edited. The tree structure in the right border lists all the expressions that can be used are functions and operators. When you click a function or operator, some explanations are automatically displayed in the description column below. You can also directly click the expected expression or expression function in the editing box instead of manually typing it.
Set the expression in the expression editor. Note that the expression value must be consistent with the expression. For example, if the delayvalidation attribute is selected, the expression result must be of a boolean type. After the settings are complete, you can verify the store evaluate expression. If the expression does not match, an error message is displayed. For example, if we enter the letter A, we will get a 4 error message.

Figure 4

Expression in task

Expressions can also be used in tasks. The most typical task is the derived Column task. Next, let's take a look at how we use it in derived column. Create a new package, drag and drop a Data Flow task in control flow, double-click it to enter data flow, drag and drop an ole db data source, and select view [HumanResources]. [vemployee]. In the columns label, select only the columns firstname, middlename, and lastname.
Add a derived Column task to connect it to the data source. Double-click derived column to open the editor. You can see the 5 interface, which is similar to the expression editor. The difference lies in the expression boundary box. It is not a text box, but a editable table used to edit the output column.

Figure 5

Enter fullname In the first column, select <add as new column> In the derived column, and enter the following expression in the expression column:
Firstname + "" + middlename + "" + lastname
This expression is very simple, except that all names are connected as a complete name separated by spaces.
Click OK to save and close the expression editing interface. Add a flat file or an Excel file to connect it to the derived column. Finally, run the package. Here we select to use the data in Excel format. After running successfully, you can see the result of 6.

Figure 6

We will find that some columns in the fullname column are empty, because some people do not have middlename, which leads to incorrect expression values. This result is not expected. We need to determine the value of middlename. Open the expression editor and edit it as follows:
Firstname + (isnull (middlename )? "": "" + Middlename + "") + lastname
Here, the isnull function is used to determine and then the question mark expression is used to process the middlename.
This function evolved from the C language. The ternary expression is similar to the IIF function in VB. Finally, save and close the editor. Finally, check the Excel file. Each row has a value, 7

Figure 7

The new expression language provides powerful functions for SSIs, making it easier to use. For more details, see SQL Server 2008 books online.