SQLServerCentral / Discuss Content Posted by Andy Leonard / Article Discussions / Article Discussions by Author / Level 3 of the Stairway to Integration Services - Adding Rows in Incremental Loads / Latest Posts

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

Rob de Vos — Thu, 04 Apr 2013 00:22:31 GMT

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 mode2 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 :-)

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

JAMESC2003 — Wed, 03 Apr 2013 14:55:33 GMT

Write a book on SSIS.I suspect it will be 200,000 pages .....The blow-by-blow (with background explanations) areimmensely helpful.I will still buy it .... even if it costs $100

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

shalinder.verma — Sun, 31 Mar 2013 22:17:59 GMT

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.

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

Brandon Forest — Fri, 29 Mar 2013 14:19:42 GMT

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. ;-)

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

Rob de Vos — Fri, 14 Dec 2012 05:39:38 GMT

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

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

rannoune — Mon, 01 Oct 2012 08:05:38 GMT

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!

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

Andy Leonard — Wed, 21 Sep 2011 11:18:22 GMT

Hi JJ, I have an excellent editor, Steve Jones.:{>

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

JJ B — Wed, 21 Sep 2011 10:41:38 GMT

You have great skill in explaining things. Thanks for taking the time to share your information in a way that is actually helpful!

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

ajenawsu — Mon, 19 Sep 2011 12:17:59 GMT

This is a great (and timely) article. I'm looking forward to the next installment. I watched your SQL Lunch presentation on re-executable T-sql and it has changed my life. Thanks.

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

Andy Leonard — Fri, 16 Sep 2011 09:48:42 GMT

Thanks Stephane, There is a way to create audit trails of record states, but that topicis beyond the scope of this series. I can get you started offline. Email me at andy.leonard@gmail.com.:{>

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

NYSystemsAnalyst — Fri, 16 Sep 2011 09:45:36 GMT

Thanks. This is a great series of articles and look forward to reading more.

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

Andy Leonard — Fri, 16 Sep 2011 09:42:57 GMT

Hi NYSystemAnalyst, That's a poorly-worded sentence. My apologies. The No Match output exists in 2008 and R2. :{>

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

Andy Leonard — Fri, 16 Sep 2011 09:40:41 GMT

Hi Raghav, You're getting ahead of me. Step 5...:{>

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

S Paquin — Fri, 16 Sep 2011 09:00:28 GMT

I just finished reading your SSIS articles and they are an excellent introduction. I just can not wait to see other articles to handle updates an deletes. I hope I will not have to wait too long...Is there any way to simulate the same as Replication and keep a log of insert, update and deletes in the source so that only those manipulations are handled in the destination ? It seems to me it is a waste of resources to have to do lookups periodically to detect changes between source and destination when SQLServer already does that job of tracking changes. Any suggestions?Keep the good work.Stephane

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

NYSystemsAnalyst — Fri, 16 Sep 2011 06:45:37 GMT

Below figure 20, there is a statement that reads, "In SSIS 2008 and SSIS 2008 R2, the Lookup Transformation provides this built-in output to catch records in the Lookup table (the dbo.Contact destination table, in this case) that do not exist in the source (the Person.Contact table) - it's the Lookup No Match Output."Shouldn't this be the other way around? " In SSIS 2008 and SSIS 2008 R2, the Lookup Transformation provides this built-in output to catch records in the [b]Source [/b]table (the dbo.Contact destination table, in this case) that do not exist in the [b]lookup [/b](the Person.Contact table) - it's the Lookup No Match Output."

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

Samuel Vella — Fri, 16 Sep 2011 03:54:13 GMT

[quote][b]raghav.kaura (9/15/2011)[/b][hr]Also how will this handle the load in case email id gets updated in the source.[/quote]If your source system uses an attribute like email as a key column and that attribute can be changed then you have a very bad source system or you're using the wrong column as a key onto the source data.

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

raghav.kaura — Thu, 15 Sep 2011 22:56:36 GMT

How do you handle a scenario when data exists in the target table but has been deleted from the source table. Also how will this handle the load in case email id gets updated in the source.

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

Andy Leonard — Sat, 20 Aug 2011 20:29:03 GMT

Comments posted to this topic are about the item [B]Level 3 of the Stairway to Integration Services - Adding Rows in Incremental Loads[/B]