Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services

Question

Post reply

Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Analytical1 SSC Rookie Points: 41 More actions · Answer 1

Analytical1

SSC Rookie

Points: 41

March 17, 2014 at 12:24 pm

#1697750

I think you're right.

NORMALNIE SSC Rookie Points: 30 More actions · Answer 2

Hello kev4king2000,

maybe like this:

BEGIN TRAN

DECLARE @ETL_HISTORY TABLE(

[Action] [varchar](50) NULL,

[New_FirstName] [nvarchar](50) NULL,

[New_MiddleName] [nvarchar](50) NULL,

[New_LastName] [nvarchar](50) NULL,

[New_EmailAddress] [nvarchar](50) NULL,

[Old_FirstName] [nvarchar](50) NULL,

[Old_MiddleName] [nvarchar](50) NULL,

[Old_LastName] [nvarchar](50) NULL,

[Old_EmailAddress] [nvarchar](50) NULL)--,

MERGE DBO.Contact T

USING Person.Contact S

ON T.EmailAddress = S.EmailAddress

WHEN MATCHED AND

(

T.FirstName <> S.FirstName OR

T.MiddleName <> S.MiddleName OR

T.LastName <> S.LastName

)

THEN UPDATE

SET

T.FirstName = S.FirstName,

T.MiddleName = S.MiddleName,

T.LastName = S.LastName

WHEN NOT MATCHED THEN INSERT

(

FirstName,

MiddleName,

LastName

)

VALUES

(

S.FirstName,

S.MiddleName,

S.LastName

)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT $action, inserted.*, deleted.* INTO @ETL_HISTORY;

SELECT * FROM @ETL_HISTORY

ROLLBACK TRAN

Kindest regards

Jerid421 Right there with Babe Points: 794 More actions · Answer 3

Andy,

I am eternally grateful for Stairway series. But if someone points out a typo like the "Person.Contact" / dbo.Contact" issue, you should fix it. This happened in an earlier lesson in the series as well, and it can take those of us following along an hour or so to figure out what in the hell went wrong. I finally just thought to look in the comments for the corrections.

Just a suggestion. But once again, thank you for taking the time to create these! Great job.

Jerid

mona Newbie Points: 3 More actions · Answer 4

Hello Andy

I really appreciate the complete and easy to follow elaboration on these series.

I have a question regarding the control flow. what I realize from the series of incremental load is that, the "Insert and update rows" data flow is concerned with detecting the changing records and update them based on conditions which we provided.

on the other hand, the " Delete rows" data flow task is related to delete rows in the destination which are no longer exist in the source.

So what I miss here is the "Apply staged updates" in the control flow. what I comprehend is that the "update" job is done in the "Insert and update rows" data flow task. so what is the responsibility of the "Apply staged updates" here?

Untitled

I would be grateful if you could articulate this a bit more.

Thanks in advance.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 5

Mona,

the insert-update block does not do the update itself - just inserts the rows that need to be updated onto a a staging table, and then the "apply staged updates" step does a single update statement from that table.

Look at the level 4 link which explains this in detail - from a performance point of view its normally better to do the updates and also the deletes in a set based manner instead of using a oledb command to do one at the time.

mona Newbie Points: 3 More actions · Answer 6

Hi frederico_fonseca

Thanks for your reply. You were right. I checked the level 4 once again and that solved my problem.