Stairway to integration services level 4 -- Translation

In this article, we will talk about incremental data update: replacing the updated data in the data source with the corresponding data in the target table. Update code

Before the operation, we should first remove the data in the target table E (DBO. Contact ).

Use AdventureWorksgoUpdate dbo.Contact Set MiddleName = ‘Ray‘ Where MiddleName Is NULL

Open my_first_ssis_project. Click the data flow tag. Modify the Level 3 project file. Double-click lookup transformation to open lookup transformation Editor:

Figure 1

Click the columns page.

In the previous article, we mapped the email fields of available input columns and available input columns for inline matching.

In the previous article, we did not select any check boxes. If we treat lookup transformation as join, selecting these fields is like adding fields to the select clause. Select all (the name above ):

 
Figure 2

3. the output alias column is the available lookup columns output column, similar to the column returned after join. I like to add the prefix "lkup _" or "DEST _" to distinguish the fields from OLE DB source and lookup transformation. if the column name is the same, SSIS will be followed by "(1 )". is prefixed with "lkup:

Figure 3

Now let's review it.

We set person. the records in the contact table are loaded into data flow, and then stored with DBO in lookup transformation. contact for record matching. if the record does not match, no match output is performed. then modify the configuration of lookup transformation. If a matching row is found, the email, firstname, lastname, and middlename columns are output.

Drag an ole db command transformation and conditional split transformation. Click lookup transformation and drag the blue data flow path to conditional split:

 
Figure 4

Because lookup no match output has been dragged to the contact, only lookup match output is left. This drag to the conditional split will not prompt selection. lookup transformation no match output and match output. in addition to the data content, there are other differences, but the most important thing is that the fields are different.

Right-click the data flow path between ole db source adapter and lookup Transformation:

Figure 5

Click the metadata page, as shown in the following figure:

Figure6

These columns are from ole db source. Turn off the data flow path and right-click the data flow path between ole db destination (contact) and lookup transformation. The metadata page is shown as follows:

Figure 7

In the previous article, we have seen that the no match output of the. lookup transformation is exactly the same as the input metadata. That is to say, if there is no matching, the input field is simply output directly.

In this section, we modified the lookup transformation configuration, so the matching record metadata will be different: the fields in the target table will be appended. right-click the data flow path between conditional split transformation and lookup transformation. metadata is shown as follows.

Figure 8

Because we used the alias when selecting a field, it is easy to see additional fields.

Remember that in the previous article, we configured no match output, but did not return fields from the queried table? These fields are not required for incremental adding records, but we need to compare the field values.

Detection of changes in SSIs

Open conditional split transformation and you can see two virtual directories: variables and columns:

Figure 9

Click the columns directory in Figure 9. We will compare the firstname, lastname, and middlename fields.

First, compare the firstname field. Click firstname and drag it to the following list:

Figure 10

After you release the mouse, you will find that the previous firstname becomes red because of verification failure. Why does the verification fail? The condition must be boolean. But firstname is a character value, so no judgment is made. The error message is as follows:

 
Figure 11

Conditional expression statement in the upper right corner

SSIS expression statements are a little difficult to learn. You can refer to my previous blog posts, which should be helpful to you.

I want to check that firstname is not equal to lkup_firstname. Open the operators virtual directory in the expression area and select the unequal OPERATOR:

Figure 12

Click the unequal operator and drag it to the right of the firstname field:

Figure 13

Then, drag lkup_firstname:

Figure 14

The expression returns a Boolean value, so the entire conditional expression turns black.

Because we have not changed the value of the firstname field, when we perform the test. the total firstname value is false (we only changed the value of middlename ). then, the other fields are included for determination. finally, rename case 1 as updated rows. for example

 
Figure 21

Note that each time a condition is created (condition). Conditional split transformation, a new output is generated to transmit data.

There is also a default output name. For example, if no condition is matched, the record will go from this output

Figure 23

Turn Off conditional split transformation, and drag the blue path to ole db command. Select the updated rows output path:
Figure 24

Now your data flow should be shown as follows:

 
Figure 25

We use ole db command transformation to update the target table. double-click ole db command transformation and select local in Connection Manager ). adventureworks, and then enter the following SQL statement in the sqlcommand attribute of the Manager Component properties label

Update dbo.Contact  Set FirstName = ?, MiddleName = ?, LastName = ? Where Email = ?

Figure 26

Then we need to map the parameter placeholder (parameter placeholders is the question mark (?)) Click the column mappings label:

Figure 27

? The tag is an array starting with 0. That is to say, param_0 corresponds to the first question mark, param_1 represents the second, and so on. We drag the corresponding fields to the parameter for ing.

Figure 28

Turn OffOle db command. Now Data Flow task should be the same:

Figure 29

Press F5 to test the result:

Figure 30

Error... Click the process tag and find some errors. However, it is difficult to read them.

Figure 31

If we can right-click the error and copy the text:

Figure 32

The error message is as follows:

[Conditional split [2] error: the expression "(firstname! = Lkup_firstname) | (middlename! = Lkup_middlename) | (lastname! = Lkup_lastname) "on" Conditional split. outputs [updated rows] "evaluated to null, but the" Conditional split "requires a Boolean results. modify the error row disposition on the output to treat this result as false (ignore failure) or to redirect this row to the error output (redirect row ). the expression results must be boolean for a conditional split. A null expression result is an error.

The error is because our expression produces a null value. If a comparison in the expression is null, the result will not be a Boolean value true or false.

Let's see which field in person. Contact has a null value:

Use AdventureWorksgoSelect * From Person.Contact Where FirstName Is NullSelect * From Person.Contact Where MiddleName Is NullSelect * From Person.Contact Where LastName Is Null

After these statements are executedLThe middlename of row 8,499 is null, which is actually the fields we changed earlier.

Let's solve this problem. Use the isnull () function to determine whether middlename is null and return a boolean result. The statement is updated as follows:

(FirstName != LkUp_FirstName) || ((ISNULL(MiddleName)?"Humperdinck": MiddleName) != (ISNULL(LkUp_MiddleName) ? "Humperdinck": LkUp_MiddleName)) || (LastName != LkUp_LastName)

Figure 33

Note that "Humperdinck" is always an invalid value. If the middlename in the source table is updated from null to Humperdinck. At this time (isnull (middlename )? "Humperdinck": middlename) the result is Humperdinck. And (isnull (lkup_middlename )? "Humperdinck": lkup_middlename) is also the result of Humperdinck. The expression cannot detect differences at all.

I use "Humperdinck" as the middle name because I like the princess bride. In practice, I will combine numbers, letters, and some difficult character combinations for matching.

Press F5 to test again:

 
Figure 34

The test was successful. However, we found that the update took about 1 minute 37 seconds:

Figure 35

Why does the execution take so long? It takes a long time to execute statements in the Main ole db command. Why? Because ole db command updates only one row at a time, just like a cursor.

Batch update set-based updates

Is there a way to avoid this row-based update mode? Let's take a look at how to deal with it. Delete the ole db command and place an ole db destination in place of it:

 
Figure 36

Rename ole db destination to "stageupdates". Double-click it. Make sure that "(local). adventureworks" and "table or view-fast load" are selected ".

Create a table:

 
Figure 37

CREATE TABLE [StageUpdates] (    [FirstName] nvarchar(50),    [LastName] nvarchar(50),    [Email] nvarchar(50),    [MiddleName] nvarchar(50))

Note:

Figure 38

Click the ings page:

Figure 39

Because available destination columns is the same as the metadata of available input columns, fields are automatically matched.

After clicking OK, 40:

Figure 40

Next we drag an execute SQL task under the data flow task to update the records in the stageupdates table to DBO. Contact:

Figure 41

Double-click Execute SQL task to change the name attribute to "Apply staged updates". Select "(local). adventureworks" for the connection attribute, and paste the following statement in the sqlstatement attribute:

Update dest Set dest.FirstName = stage.FirstName  , dest.MiddleName = stage.MiddleName  , dest.LastName = stage.LastName From dbo.Contact dest   Join dbo.StageUpdates stage    On stage.Email = dest.Email

Modify the following DBO. Contact table and execute the SSIS package:

Use AdventureWorksgoUpdate dbo.Contact Set MiddleName = ‘Ray‘ Where MiddleName Is NULL

Figure 42

Data Flow task:

Figure 43

Execution time improved!

Figure 44

We also need to process the following stageupdates tables. We should clear the records before execution to avoid repeated data import during execution. we need to place an execute SQL task to control flow and connect the data flow task:

Figure 45

Double-click Execute SQL task configuration information as follows:

 
Figure 46

Run the following command again:

Figure 47

Figure 48

So far, incremental updates are completed.

Address: http://www.sqlservercentral.com/articles/Stairway+Series/76390/

Stairway to integration services level 4 -- Translation