InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 19:07:16 GMT20http://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxAndy,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:[font="Courier New"]Delete destFrom [b]dbo.Contact[/b] destJoin StageDeletes stageOn stage.Email = dest.Email[/font]The table should have been dbo.Contact instead of Person.Contact. And the test script should change to:Use AdventureWorksgoSelect Count(*) As RecCount From [b]dbo.Contact[/b] Where FirstName = 'Andy' And LastName = 'Leonard'Thu, 20 Dec 2012 15:12:12 GMTbhaktapurhttp://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxHi 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.":{&gt;Fri, 21 Sep 2012 13:10:42 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxAndy, 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?Fri, 21 Sep 2012 08:11:05 GMTFatherjackhttp://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxHi 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!MichelleWed, 01 Aug 2012 07:47:04 GMTmkheirkhah-1106726http://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxSQL 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 :-)Thu, 26 Jan 2012 06:00:26 GMTStephen.Richardsonhttp://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxHi 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.ThanksWed, 25 Jan 2012 18:35:15 GMTkev4king2000http://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxThe final test query should also be Use AdventureWorksgoSelect 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.Sun, 08 Jan 2012 22:47:57 GMTJoe Kornhttp://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxI 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?Mon, 12 Dec 2011 06:42:20 GMTStephen.Richardsonhttp://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxHi 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!AndyFri, 04 Nov 2011 14:23:42 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxHi David, You are correct, but I do not plan to cover the Merge statement in this series.AndyFri, 04 Nov 2011 14:22:11 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspx[quote][b]David Data (11/4/2011)[/b][hr]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?[/quote]^^^ 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.- SavageFri, 04 Nov 2011 12:37:46 GMTNbleSavage-393985http://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspx[i] Delete src From Person.Contact src Join StageDeletes stage On stage.Email = src.EmailAddress[/i]I think it should be deleted from dbo.ContactFri, 04 Nov 2011 10:07:31 GMTerin.northhttp://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxAs 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?Fri, 04 Nov 2011 05:06:11 GMTDavid Datahttp://www.sqlservercentral.com/Forums/Topic1200364-208-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Integration+Services+(SSIS)/76395/">Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services</A>[/B]Thu, 03 Nov 2011 22:49:06 GMTAndy Leonard