SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Level 3 of the Stairway to Integration Services - Adding Rows in Incremental Loads


Level 3 of the Stairway to Integration Services - Adding Rows in Incremental Loads

Author
Message
JJ B
JJ B
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 2859
You have great skill in explaining things. Thanks for taking the time to share your information in a way that is actually helpful!
Andy Leonard
Andy Leonard
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1158 Visits: 1095
Hi JJ,

I have an excellent editor, Steve Jones.

:{>

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
rannoune
rannoune
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 33
Hi Andy,

Thanks for this great article!!! I have a question; what if the Person.Contacts table is located on an another server, what would you do? We definitely don't want to get all rows as it impacts performance and may have timeout issues for huge data. How can we join Person.Contact and dbo.Contact when they are located on different servers, then only select the rows we need to insert from Person.Contact?

Thanks again!
Rob de Vos
Rob de Vos
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 214
Andy I'am enjoing the series but I have a small problem with the 'Update'.

I'ts working, but it only updates about 700 records each run, I can't find any option to change this behavior.
The only thing I can imagine is that I use a very limited virtual server and that this is caused by the Full Cache option of the Lookup.
But I see the correct number of records at each Data Flow.

I do see 2 warnings, but I don't think they have any thing to do with the update problem:
[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.

and
[SSIS.Pipeline] Warning: The output column "LkUp_BusinessEntityID" (131) on output "Lookup Match Output" (103) and component "Lookup" (101) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

Rob
Brandon Forest
Brandon Forest
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 446
I agree that the second method is better than the first, but the duration of each method can only be measured correctly if you clear the procedure cache for the previous dataset. ;-)
shalinder.verma
shalinder.verma
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 170
This is a good article for the beginners. Great work Andy!

I would also stress on looking at Merge TSQL statement. Have used it quite a few times. It would be interesting to compare the performance and pros/cons with Lookup transformation.

Also, with Merge we can handle SCD1 and SCD2 quite easily. I reckon this is required in most scenarios for incremental dimension loading. This way we can do away with bad SCD transformation available with SSIS out of the box.

My 2 cents.
JAMESC2003
JAMESC2003
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 69
Write a book on SSIS.
I suspect it will be 200,000 pages .....
The blow-by-blow (with background explanations) are
immensely helpful.

I will still buy it .... even if it costs $100
Rob de Vos
Rob de Vos
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 214
in fact there is a book about SSIS although the title suggests something else..
the title is Implementing a Data Warehouse with Microsoft SQL server 2012 (exam 70-463)
It covers a lot of SSIS but also Master data services, Data Cleansing and Data Warehouse, most of the tasks are done using SSIS.

I found 2 problems,
1 the ODBC connections only work in 32-bit mode
2 the Master Data web interface is in Dutch I have configured the server to be in my home country, suddenly the exercises are a real puzzle :-)
courtney.fletcher
courtney.fletcher
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 14
I agree but I don't see a reply. Did he reply to you?
Rob de Vos
Rob de Vos
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 214
no, never received a reply.
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