Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    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...
Add to briefcase
14 posts, Page 1 of 2
12»»

Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Andy Leonard
Posted Thursday, November 03, 2011 10:49 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375, Visits: 947
Comments posted to this topic are about the item Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1200364
David Data
Posted Friday, November 04, 2011 5:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:28 AM
Points: 115, Visits: 719
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?
Post #1200481
erin.north
Posted Friday, November 04, 2011 10:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 11:56 AM
Points: 36, Visits: 184
Delete src
From Person.Contact src
Join StageDeletes stage
On stage.Email = src.EmailAddress

I think it should be deleted from dbo.Contact
Post #1200734
NbleSavage-393985
Posted Friday, November 04, 2011 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 4:00 AM
Points: 12, Visits: 284
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 :)

Peace.

- Savage
Post #1200839
Andy Leonard
Posted Friday, November 04, 2011 2:22 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

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

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

Andy

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1200893
Andy Leonard
Posted Friday, November 04, 2011 2:23 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375, Visits: 947
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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1200896
Stephen.Richardson
Posted Monday, December 12, 2011 6:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 21, 2012 6:44 AM
Points: 26, 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?
Post #1220216
Joe Korn
Posted Sunday, January 08, 2012 10:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 03, 2013 8:08 AM
Points: 66, Visits: 675
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.
Post #1232199
kev4king2000
Posted Wednesday, January 25, 2012 6:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 16, 2012 1:27 AM
Points: 7, 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
Post #1241869
Stephen.Richardson
Posted Thursday, January 26, 2012 6:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 21, 2012 6:44 AM
Points: 26, 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
Post #1242099
« Prev Topic | Next Topic »

Add to briefcase
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.