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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

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

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
David Data
David Data
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1589 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 (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 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 (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: 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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4333 Visits: 1119
Hi David,

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

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
Andy Leonard
Andy Leonard
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4333 Visits: 1119
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, Chief Data Engineer, Enterprise Data & Analytics
Stephen.Richardson
Stephen.Richardson
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 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
Old Hand
Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)

Group: General Forum Members
Points: 308 Visits: 753
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 (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 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
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 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