SQL Clone
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
Andy Leonard
Andy Leonard
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3447 Visits: 1113
Comments posted to this topic are about the item Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
David Data
David Data
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1297 Visits: 828
As you're now using a staging table, you could do all three ADD/DELETE/UPDATE operations at once with an SQL MERGE. Are you going to cover that in the next part of the series?
erin.north
erin.north
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 274
Delete src
From Person.Contact src
Join StageDeletes stage
On stage.Email = src.EmailAddress


I think it should be deleted from dbo.Contact
NbleSavage-393985
NbleSavage-393985
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 285
David Data (11/4/2011)
As you're now using a staging table, you could do all three ADD/DELETE/UPDATE operations at once with an SQL MERGE. Are you going to cover that in the next part of the series?



^^^ THIS ^^^, yes please! I believe the MERGE function still must be contained within a script task / execute T-SQL task (no SSIS component built for it...yet...) but its so powerful its worth the extra overhead Smile

Peace.

- Savage
Andy Leonard
Andy Leonard
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3447 Visits: 1113
Hi David,

You are correct, but I do not plan to cover the Merge statement in this series.

Andy

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
Andy Leonard
Andy Leonard
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3447 Visits: 1113
Hi Erin,

You are correct - my intent was to delete the rows from the Destination table (dbo.Contact), not the source (Person.Contact).
Good catch and my bad!

Andy

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
Stephen.Richardson
Stephen.Richardson
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 133
I recently was surprised to find importing a large number of wide rows it was faster to use a SSIS merge of two sorted inputs with a conditional split rather than a lookup diverting no matches to a no match connection. Have you seen a general rule when it is optimal to use the merge rather than a lookup?
Joe Korn
Joe Korn
SSC Veteran
SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)

Group: General Forum Members
Points: 282 Visits: 750
The final test query should also be

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

If you run the query before executing the package you get a count of 1. After executing the package the count is 0.
kev4king2000
kev4king2000
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 36
Hi Davide,

How can one use the three ADD/DELETE/UPDATE operations at once with SQL MERGE?
I would really like to have a clue of how it works.

Thanks
Stephen.Richardson
Stephen.Richardson
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 133
SQL 2008 BOL "ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/952595a6-cf1d-4ff5-8927-66f9090cf79d.htm". MS finally did something nice. It has it's limitations and place but doesn't everything. I use it quite a bit, but would really like to see it added to SSIS. Enjoy :-)
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