Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services Expand / Collapse
Author
Message
Posted Thursday, November 3, 2011 10:49 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:15 PM
Points: 388, Visits: 1,044
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
Posted Friday, November 4, 2011 5:06 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 3:20 PM
Points: 115, Visits: 747
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
Posted Friday, November 4, 2011 10:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 1:45 AM
Points: 42, Visits: 232
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
Posted Friday, November 4, 2011 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:45 PM
Points: 12, 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 :)

Peace.

- Savage
Post #1200839
Posted Friday, November 4, 2011 2:22 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:15 PM
Points: 388, Visits: 1,044
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
Posted Friday, November 4, 2011 2:23 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:15 PM
Points: 388, Visits: 1,044
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
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
Posted Sunday, January 8, 2012 10:47 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:24 PM
Points: 132, Visits: 688
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
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
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 12»»

Permissions Expand / Collapse