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 Wednesday, August 1, 2012 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 9, 2014 8:14 AM
Points: 1, Visits: 120
Hi 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!

Michelle
Post #1338538
Posted Friday, September 21, 2012 8:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 278, Visits: 568
Andy, 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?
Post #1362691
Posted Friday, September 21, 2012 1:10 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:44 PM
Points: 389, Visits: 1,041
Hi 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."

:{>


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1362940
Posted Thursday, December 20, 2012 3:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 3:04 PM
Points: 7, Visits: 283
Andy,
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:


Delete dest
From dbo.Contact dest
Join StageDeletes stage
On stage.Email = dest.Email


The table should have been dbo.Contact instead of Person.Contact. And the test script should change to:

Use AdventureWorks
go
Select Count(*) As RecCount
From dbo.Contact
Where FirstName = 'Andy'
And LastName = 'Leonard'
Post #1399120
Posted Wednesday, August 7, 2013 10:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 5:35 AM
Points: 16, Visits: 103
Hi Andy,

Appreciate your good stuffs.. I know it is very late comment to this post. However I like to share my opinion and get your thoughts as well.

1. From your scenario, my view of the incremental data means the data that has been amented/inserted since certain time. according to this logic, the extracted source data in this article is not incremental one. it is FULL SET. if my assumption / definition is not correct, please advise me.

2. Lookup transformation with "cache" will degrade the performance for huge amounts of data due to memory usage. my opinion is that it can not be used for transactional tables (or fact tables). But surely it helps for small lookups table ( or dimension tables)

3. Lookup transformation with "no cache" again degrade a lot when we use the FULL DATA SET as it make millions of calls to the database

4. my option is "use the MERGE JOIN " route for delete operation by selecting only key colunms and compare them to identify the missing ones..
to be honest , I am a LOOKUP TRANSFORAMTION lover, but no other go for DELETE OPERATION as I am aware of. Requesting you provide me any other route/ logic


looking forward to hear your thoughts..

Thanks again for such a wonderful series,

Have a good day!!!

K.Singanan
Post #1481974
Posted Monday, March 17, 2014 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 12:23 PM
Points: 1, Visits: 20
I think you're right.


Post #1551869
Posted Friday, May 23, 2014 5:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 2, Visits: 29

Hello kev4king2000,

maybe like this:


BEGIN TRAN
DECLARE @ETL_HISTORY TABLE(

[Action] [varchar](50) NULL,
[New_FirstName] [nvarchar](50) NULL,
[New_MiddleName] [nvarchar](50) NULL,
[New_LastName] [nvarchar](50) NULL,
[New_EmailAddress] [nvarchar](50) NULL,
[Old_FirstName] [nvarchar](50) NULL,
[Old_MiddleName] [nvarchar](50) NULL,
[Old_LastName] [nvarchar](50) NULL,
[Old_EmailAddress] [nvarchar](50) NULL)--,



MERGE DBO.Contact T
USING Person.Contact S
ON T.EmailAddress = S.EmailAddress
WHEN MATCHED AND
(
T.FirstName <> S.FirstName OR
T.MiddleName <> S.MiddleName OR
T.LastName <> S.LastName
)
THEN UPDATE
SET
T.FirstName = S.FirstName,
T.MiddleName = S.MiddleName,
T.LastName = S.LastName
WHEN NOT MATCHED THEN INSERT
(
FirstName,
MiddleName,
LastName
)
VALUES
(
S.FirstName,
S.MiddleName,
S.LastName
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE

OUTPUT $action, inserted.*, deleted.* INTO @ETL_HISTORY;
SELECT * FROM @ETL_HISTORY
ROLLBACK TRAN


Kindest regards

Post #1573969
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse