Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
mkheirkhah-1106726
mkheirkhah-1106726
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 120
Hi Andy,

Great series (integration services)! It was very helpful -- easy to follow and extremely relevant. I just have one thing I believe is an error. It was on the last part (delete rows). The final query to check if rows were deleted went against the person.contact table. However, the row was actually added to the dbo.contact table. So I believe the last query should also go against the dbo.contact table. Just an Fyi. Thanks for your great articles!

Michelle
Fatherjack
Fatherjack
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 627
Andy, for the TSQL step to apply updates and Deletes to the destination database, would you recommend adding indexes to the stage tables that match the indexes on the production tables (I'm assuming that in reality the join will be on some unique (or close to) identifier rather than an email address) to help the optimiser and therefore speed the package processing time?
Andy Leonard
Andy Leonard
Mr or Mrs. 500
Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)

Group: General Forum Members
Points: 505 Visits: 1092
Hi Jonathan,

If it would improve performance, sure. I am unsure how much performance improvement could be realized unless there are a lot of staged rows, though. But as with many things related to SQL Server, I am certain the answer is "it depends."

:{>

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
bhaktapur
bhaktapur
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 298
Andy,
Thank you for the great post. This tutorial helped me a lot. I think your delete script is deleting from the source table instead of the destination table. The script should have been:


Delete dest
From dbo.Contact dest
Join StageDeletes stage
On stage.Email = dest.Email


The table should have been dbo.Contact instead of Person.Contact. And the test script should change to:

Use AdventureWorks
go
Select Count(*) As RecCount
From dbo.Contact
Where FirstName = 'Andy'
And LastName = 'Leonard'
Singanan Krishnasamy
Singanan Krishnasamy
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 125
Hi Andy,

Appreciate your good stuffs.. I know it is very late comment to this post. However I like to share my opinion and get your thoughts as well.

1. From your scenario, my view of the incremental data means the data that has been amented/inserted since certain time. according to this logic, the extracted source data in this article is not incremental one. it is FULL SET. if my assumption / definition is not correct, please advise me.

2. Lookup transformation with "cache" will degrade the performance for huge amounts of data due to memory usage. my opinion is that it can not be used for transactional tables (or fact tables). But surely it helps for small lookups table ( or dimension tables)

3. Lookup transformation with "no cache" again degrade a lot when we use the FULL DATA SET as it make millions of calls to the database

4. my option is "use the MERGE JOIN " route for delete operation by selecting only key colunms and compare them to identify the missing ones..
to be honest , I am a LOOKUP TRANSFORAMTION lover, but no other go for DELETE OPERATION as I am aware of. Requesting you provide me any other route/ logic


looking forward to hear your thoughts..

Thanks again for such a wonderful series,

Have a good day!!!

K.Singanan
Analytical1
Analytical1
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 20
I think you're right.



NORMALNIE
NORMALNIE
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 63
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
Jerid421
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 42
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search