SSIS Design Pattern - Incremental Loads

SSIS Design Pattern - ETL Instrumentation, Part 3

Applied Business Intelligence — Mon, 19 Nov 2007 03:43:41 GMT

Introduction This post is part of a series of posts on ETL Instrumentation. In Part 1 we built a database

re: SSIS Design Pattern - Incremental Loads

jacorradine — Sun, 09 Mar 2008 18:05:37 GMT

This article rules!!!.. this is exactly what I was looking for! :)

re: SSIS Design Pattern - Incremental Loads

Jan SOtola — Thu, 10 Apr 2008 11:03:12 GMT

Great howto!

Just one improvement:

I think that an expression like "(ColA != Dest_ColA)" is not enough for NULLable columns.

The SSIS engine (at least in Visual Studio debug mode) evaluates NULLs as SQL server - for any relational operator

NULL relop ANYTHING = false

Thus, if ColA allows NULLs, the expression have to be:

"(ColA != Dest_ColA) || (Isnull(ColA) != Isnull(Dest_ColA))"

re: SSIS Design Pattern - Incremental Loads

andy — Thu, 10 Apr 2008 11:35:54 GMT

Hi Jan,

Thanks!

You are correct about nullable columns. In this description I chose to demonstrate the principles of incremental loads with SSIS 2005 - this is not production-ready code.

Thanks for providing an example of code that is more production-ready!

:{> Andy

re: SSIS Design Pattern - Incremental Loads

Peiling — Thu, 10 Apr 2008 20:32:21 GMT

This article is very helpful and exactly what I am looking for. However the text are truncated on the right side and some graphics are not displaying. Can you modify it? I really need those details. Thanks.

re: SSIS Design Pattern - Incremental Loads

Tab Alleman — Tue, 22 Apr 2008 18:02:25 GMT

Further clarification of the Nullable columns: Does that expression use the T-SQL IsNull() function, in which case it should be expressed like this:

IsNull(ColA,'')!=IsNull(Dest_ColA,'')

Or the VB-style IsNull(), in which case, I would think you'd want this:

(ColA != Dest_ColA) || (Isnull(ColA) AND Isnull(Dest_ColA))

or would you use "&&" instead of "AND"? I have no idea what languages are allowed in this expression. The use of || implies it's either C# or Java or something like that.

And finally, instead of using a bunch of RELOP || RELOP || RELOP statements, wouldn't it be possible to do a CHECKSUM() or other sort of Concat comparison?

re: SSIS Design Pattern - Incremental Loads

anitalala — Sat, 06 Dec 2008 21:44:19 GMT

Thank you! It was exactly what I needed. I wish I had found this earlier. It could have saved me a lot of time.

re: SSIS Design Pattern - Incremental Loads

jkgerrik — Tue, 09 Dec 2008 10:57:01 GMT

The article is great! But what about type 2 updates for slowly changing dimensions?

re: SSIS Design Pattern - Incremental Loads

escaleraroyal — Wed, 24 Dec 2008 08:42:53 GMT

It's pretty fast. But it only loads 3 records. Wonder how the performance will be for 500+ million records source tables. Currently I'm designing an incremental load for 20 tables from Oracle 10G to SS2K5. If you have any suggestions, ideas about how to help me design my project. You can email me at leinad.yang@gmail.com. Thanks

re: SSIS Design Pattern - Incremental Loads

jcaiola — Tue, 07 Apr 2009 14:23:35 GMT

Wow - this is perfect for what I was looking for. New to SSIS. Have one question - you mentioned that since this is row by row, there is a better way to do this for large amounts of data (Note the query is executed on a row-by-row basis. For performance with large amounts of data, you will want to employ set-based updates instead.). Can oyu elaborate on that or tell me where I should look to find that information?

Thanks again.

re: SSIS Design Pattern - Incremental Loads

jcaiola — Tue, 07 Apr 2009 17:00:10 GMT

One more comment here. I also noticed that this does not update rows that have been removed from the source database. Correct?

Pretty new to this...

Design Pattern for Database Incremental Loads. - Make IT Complete

Design Pattern for Database Incremental Loads. - Make IT Complete — Thu, 21 May 2009 22:16:37 GMT

PingBack from http://blog.makeitcomplete.com/2009/05/design-pattern-for-database-incremental-loads/

Increment Database Loads - Make IT Complete

Increment Database Loads - Make IT Complete — Thu, 21 May 2009 22:18:32 GMT

PingBack from http://blog.makeitcomplete.com/2009/05/increment-database-loads/

SSIS: Insert New, Update existing — how? | it.rss24h.com

SSIS: Insert New, Update existing — how? | it.rss24h.com — Fri, 22 Jan 2010 04:39:28 GMT

PingBack from http://it.rss24h.com/index.php/ssis-insert-new-update-existing-how/