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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Leonard » Deleting Rows in Incremental Loads – Level 5...

14 posts, Page 2 of 2

««1 2

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

Rate Topic

Display Mode

Topic Options

Author

Message

mkheirkhah-1106726

Posted Wednesday, August 01, 2012 7:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 11:44 AM
Points: 1, Visits: 102

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

Post #1338538

Fatherjack

Posted Friday, September 21, 2012 8:11 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:42 AM
Points: 273, Visits: 450

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?

Post #1362691

Andy Leonard

Posted Friday, September 21, 2012 1:10 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375, Visits: 947

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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard

Post #1362940

bhaktapur

Posted Thursday, December 20, 2012 3:12 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 3:04 PM
Points: 7, Visits: 283

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'

Post #1399120

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions