THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

SSIS Design Pattern - Incremental Loads

This blog has moved! You can find this content at the following new location:

Published Monday, July 9, 2007 3:13 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Alberto Ferrari said:

Andy, maybe you are interested in taking a look at the TableDifference component I published at

It is an all-in-one and completely free SSIS component that handles these kind of situations without the need to cache data in the Lookup. Lookups are nice but - in real situaton - they may shortly lead to out of memory situations (think at a hundred million rows table... it simply cannot be cached in memory).

Beware that - for huge table comparison - you will need both TableDifference AND the FlowSync component that you can find at the same site.

I'll be glad to hear your comments about it.


July 12, 2007 5:21 AM

andyleonard said:

Thanks Alberto! Checking it out now.

:{> Andy

July 13, 2007 9:30 PM

David R Buckingham said:

Thank you greatly Andy.  This couldn't have come at a better time as I just started using Integration Services for the first time on Friday to handle eight different data loads (all for a single client).  Four of the data loads are straight appends, but the other four are incremental.

This approach is vastly superior to loading the incremental data into a temporary table and then processing it against the destination table.  In fact, it proved to be more efficient than both set-based insert/updates or a cursor-based approach.  Yes, I tested both approaches prior to implementing yours.  Your approach was faster than the set-based insert/updates even though I tested it across the WAN which suprised me greatly.

I also created a script to assist with the creation of the Conditional Split "Changed Rows" condition which follows (be sure your results aren't being truncated when you have a table with many columns):


DECLARE @Filter varchar(max)

SET @Filter = ''

-- ((ISNULL(<ColumnName>)?"":<ColumnName>)!=(ISNULL(Dest_<ColumnName>)?"":Dest_<ColumnName>)) ||

SELECT @Filter = @Filter + '((ISNULL(' + c.[name] + ')?"":' + c.[name] + ')!=(ISNULL(Dest_' + c.[name] + ')?"":Dest_' + c.[name] + ')) || '

FROM sys.tables t

INNER JOIN sys.columns c

ON t.[object_id] = c.[object_id]

WHERE SCHEMA_NAME( t.[schema_id] ) = 'GroupHealth'

AND t.[name] = 'ConsumerDetail'

AND c.[is_identity] = 0

AND c.[is_rowguidcol] = 0



SET @Filter = LEFT( @Filter, LEN( @Filter ) - 2 )

SELECT @Filter

--- END SCRIPT ---

Again, thanks greatly.  I now have 2 SSIS books on there way to me.  I am eager to learn as much as I can.

July 17, 2007 3:52 PM

Bill Mo said:

Hello,Andy!Thanks a lot for your incremental process!I'm doing SSIS project!

July 17, 2007 9:47 PM

david boston said:

Thanks this worked a treat for my SSIS project.

July 20, 2007 5:01 AM

andyleonard said:

Hi David, Bill, and David,

  Thanks for the feedback!

:{> Andy

August 8, 2007 7:14 PM

saul said:

Hi Andy !!  Great work... I was scared because of this Incremental load... and you saved my weekend... now I can enjoy it .... :-)

September 7, 2007 5:56 PM

Steve Hall said:

Anyone had a problem with the insert and update commands locking each other out?

Didn't happen at first but does now.  Update gets blocked by the insert and it just hangs.


September 18, 2007 1:18 PM

andyleonard said:

Thanks Saul!

Steve, are you sure there's not something more happening on the server that's causing this?

If this is repeatable, please provide more information and I'll be happy to take a look at it.

SQL Server does a fair job of detecting and managing deadlocks when they occur. I haven't personally seen SQL Server "hang" since 1998 - and then it was due to a failing I/O controller.

:{> Andy

September 27, 2007 6:57 PM

Bill Mo said:

Hi,Andy! I have a same problem with Steve,it is block. When bulk insert and update happen,Update gets blocked by the insert and it just hangs!Insert's wait type is ASYNC_NETWORK_IO.

October 8, 2007 4:15 AM

Bobby said:

Thx 4 the trick with Fail -> Left Join ! I was thinking how to do it whole day :o)

October 18, 2007 1:23 AM

Andy Leonard said:

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

November 18, 2007 10:53 PM

Michael Ross said:


This most certainly can be the case with larger datasets.  In my case, I ran into this issue with large FACT table loads.  Either consider dumping the contents of the insert into a temp table or SSIS RAW datafile and complete the insert in a separate dataflow task or modify the isolationlevel of the package.  Be warned, make sure you research the IsolationLevel property thoroughly before making such a change.

November 26, 2007 12:03 PM

Michael said:

What happens when a field is NULL in the destination or source when determining changed rows? Don't we need special checks to ensure if a destination field is NULL the source should also be? Thus a change has occured and the record should be updated?

December 26, 2007 10:26 AM

andyleonard said:

Hi Michael,

  Excellent question! This post was intended to cover the principles of Incremental Loads, and not as a demonstration of production-ready code. </CheesyExcuse>

  There are a couple approaches to handling NULLs in the source or destination, each with advantages and disadvantages. In my opinion, the chief consideration is data integrity and the next-to-chief consideration is metadata integrity.

  A good NULL trap can be tricky because NULL == NULL should never evaluate to True. I know NULL == NULL can evaluate to True with certain settings, but these settings also have side-effects. And then there's maintenance to consider... basically, there's no free lunch.

  A relatively straightforward method involves identifying a value for the field that the field will never contain (i.e. -1, "(empty)", or even the string "NULL") and using that value as a substitute for NULL. In the SSIS expression language you can write a change-detection expression like:

(ISNULL(Dest_ColA) ? -1 : Dest_ColA) != (ISNULL(ColA) ? -1 : ColA)

  But again, if ColA is ever -1 this will evaluate as a change and fire an update. Why does this matter? Some systems include "number of updated rows" as a validation metric.

:{> Andy

December 26, 2007 12:50 PM

Michael said:

Hi Andy,

Thanks for this great article!

Do you have any hints for implementing your design with an Oracle Source. I am attempting to incrementally update from a table with 7 million rows with ~50 fields. The Lookup Task failed when I attempted to use it like you described above due to a Duplicate Key error...cache is full. I googled this and found an article suggesting enabling restrictions and enabling smaller cache amounts. However it is now extremely slow. Do you have any experience/advice on tweaking the lookup task for my environment?

Is there value in attempting to port this solution to an Oracle to SQL environment?

Is there a way to speed things up/replace the lookup task by using a SQL Execution Task which calls a left outer join?

Is there major difference\impact in having multiple primary keys?

Thanks Again

December 26, 2007 1:47 PM

Andy Leonard said:

Now that our 5-month old son - Riley Cooper - is on the mend , I am hitting the speaking trail again!

January 6, 2008 6:16 PM

Jigs said:

Hi AndY looks great and work also great but if there are more records to update than it just hangs while doing insert and update so what should i do there any workaround by which we can avoid hanging od SSIS pacage. Please Suggest



January 15, 2008 3:36 PM

andyleonard said:

Hi Bill and Jigu,

Although I mention set-based updates here I did not demonstrate the principle because I felt the post was already too long - my apologies.

I have since written more on Design Patterns. Part 3 of my series on ETL Instrumentnation ( demonstrates set-based updates.

I need to dedicate a post to set-based updates.

:{> Andy

January 16, 2008 7:10 AM

Jai said:

Hi Andy

Thanks you did great help to understand data update through SSIS


April 5, 2008 6:16 PM

Kenneth said:

Hi Andy,

I have a hard time following your instructions. Can you send me your sample project

Thank You


July 29, 2008 1:44 PM

andyleonard said:

Hi Kenneth,

  Sorry to hear you're having a hard time with my instructions.

  One of the last instructions is a link at the bottom of the page called "Get the code". It points to this URL:

Hope this helps,


July 29, 2008 1:59 PM

EAD said:

Not sure posted same question few places….May be you gurus can explain

In SSIS Fuzzy grouping objects creates some temp tables and does the Fuzzy logic. I ran the trace to see how it does in one cursor it is taking very long time to process 150000 records. Same executes fine in any other test environments.  The cursor is simple and I can post if needed. Any thoughts ?

September 11, 2008 8:45 PM

LNelson said:

I have a similar package I am trying to create and this was a big help.  The new rows write properly however I am getting an error on the changed rows because the SQL table i am writing to has an auto incremented identity spec column.  The changes won't write to the SQL table.  If I uncheck "keep identity" it writes new rows instead of updating existing.  What am I missing?

December 1, 2008 11:38 AM

FDA said:

Thanks a lot of Andy!! Very Helpful!

December 17, 2008 3:48 AM

Rajesh said:

Hi Andy..

  Thats the good alternative for slowly changing dimention...!!

  Welll done...

  What if the increamental is based on more than one columns...?

  And further to increase the complications, if any of the column

  included in the look up condition changes as well....?

  Last one...wht if the row is deleted from source....?

January 6, 2009 3:23 AM

Ken ( said:

it looks like your package handles new and updated rows.

I don't see the code handling the deleted rows in source (asume that there is)

Here is my two cents.

in your lookup, you can split out the match and non-match rows.

non match means new record and you can do an insert directly after the lookup. you can elimninate the 'new row' in your condition in 'conditional split'

However, overall, your sample package is the best (at far as I have searched) sample on the net ( I love it, honestly).

Keep up the great work and giving out sample package.

Like most people, I do appreciate your efford.


January 7, 2009 8:10 PM

andyleonard said:

Hi Ken,

  Thanks for your kind words.

  I believe you're referring to functionality new to the SSIS 2008 Lookup Transformation - there is no Non-Match Rows output buffer in the SSIS 2005 Lookup Transformation.

:{> Andy

January 7, 2009 9:58 PM

RVS said:

Hi Andy,

Thanks a lot for this article. It proved to be a great help for me.

I was wondering if you can provide some solution to handle deleted rows from source table using lookup. I need this because I have to keep the historical data in the data warehouse.

Thanks in advance,


January 21, 2009 3:04 AM

Charlie Asbornsen said:

Andy, thanks for your help and effort.  This is definitely more elegant than staging over to one database and then doing ExecuteSQLs to execute incremental loads.

January 21, 2009 5:16 PM

Charlie Asbornsen said:

And re ranvijay's question, I would assume that when the row exists in the destination but not the source, the source RowID would show up as null, so you could do that as another split on the conditional.

January 21, 2009 5:18 PM

andyleonard said:

Hi RVS and Charlie,

  RVS, Charlie answered your question before I could get to it! I love this community!

  I need to write more on this very topic. New features in SQL Server 2008 change this and make the Deletes as simple as New and Updated rows.

  I didn't mention Deletes in this post because the main focus was to get folks thinking about leveraging the data flow instead of T-SQL-based solutions (Charlie, in regards to your first comment). There's nothing wrong with T-SQL. But a data flow is built to buffer (or "paginate") rows. It bites off small chunks, acts on them, and then takes another bite. This greatly reduces the need to swap to disk - and we all know the impact of disk I/O on SQL Server performance.

  Charlie is correct. The way to do Deletes is to swap the Source and Destinations in the Correlate / Filter stages.

  Typically, I stage Deletes and Updates in a staging table near the table to be Deleted / Updated. Immediately after the data flow, I add an Execute SQL Task to perform a correlated (inner joined) update or delete with the target table. I do this because my simplest option inside a data flow is row-based Updates / Deletes using the OLE DB Command transformation. A set-based Update / Delete is a lot faster.

  I need to write more about that as well...

:{> Andy

January 21, 2009 5:29 PM

Charlie Asbornsen said:


Looks like I have some rewriting to do on the next version of the ETL.  It's a good thing I enjoy working in SSIS!

I'm working on building a data warehouse and BI solution for a government customer, and a lot of their 1970's era upstream data sources don't have ANY kind of data validation.  In fact when we first installed in production we found out that they had some code fields in their data tables with a single quote for data!  It played merry hob with our insert statements until we figured out what was happening. Then I got to figure out how to do D-SQL whitelisting with VB scripting in SSIS :)

Of course since its the government we'll probaby have to wait until 3Q 2010 before we're allowed to upgrade to SQL 2008.  We were all gung ho about VS 2008 (which we were allowed to get) but imagine my chagrin when I found out that I couldn't use my beloved BI Studio without SQL 2008... :P  So I'll be using this for the next version... and possibly the version after that as well. Thanks a bunch!

January 21, 2009 5:41 PM

Charlie Asbornsen said:

Me again.

I think I made a mistake.  If a row already exists in the destination table and it no longer exists in the source table, I want it deleted (sent to the deletes staging table).  However, the lookup limits the row set in memory to items that are already in the source table, so its not really functioning as an outer join.  Its perfect for determining inserts and updates, but I need to do something else to do deletes...

I'm going to try adding an additional OLE DB source and point that at the same table the lookup is checking... hmm, maybe try the Merge?  I'll see what happens and let you know.

January 22, 2009 12:41 PM

Charlie Asbornsen said:

Actually I think I need a second pass... grrr.

January 22, 2009 12:44 PM

Charles Asbornsen said:


Please feel free to combine this with the previous reply.

What I wound up doing was creating a second data flow after the one that split the inserts and updates out.  The deletes flow populated a deleted rows staging table with the deleted row id, which then was joined to the ultimate destination table in a delete command in an Execute SQL task.  I would up reversing the lookup, but used the same technique by using a conditional split on whether or not the new column from the lookup was null, and if it was, the output went to the "deleted records" path, which populated the staging table.

The reason I want to actually remove the data from the table as opposed to merely marking it as deleted is because the reason a row would disappear would be because it was a bad reference code in the first place.  My big datawarehouse ETL adds new reference codes to the reference tables (which it needs to create in the first place because the source reference codes are held in these five gigantic tables which do not lend themselves to generating NV lists) for unmatched codes in the data tables (remember there's no validation at the source).  

When the reconciliation stick finally gets swung and the customer replaces the junk code it disappears from my ETL and I remove it from my table.  It is different from a code that gets obsoleted; there's a reason to track those, but garbage just needs to be thrown out.

Thanks again, I would have been very annoyed with myself if I wound up doing row-based IUDs...

January 22, 2009 2:55 PM

andyleonard said:

Hi Charles,

  I wasn't clear in my earlier response but you figured it out anyway - apologies and kudos. You do need to do the Delete in another Data Flow Task.

  Excellent work!

:{> Andy

January 22, 2009 4:15 PM

Charles Asbornsen said:


Is there a limit to how many comparisons you can make in the Conditional Split Transformation Editor?  I have a table with 20 columns, and I'm trying to do 19 comparisons.  It's telling me that one of the columns doesn't exist in the input column collection.  I can cut the expression and paste it back in and it picks a different column to complain about.  Error 0xC0010009... it says the expression cannot be parsed, may contain invalid elements or might not be well formed, and there may also be an out-of-memory error.

I've been looking at it for 1/2 an hour and all the columns it is variously complaining about are present in the input column collection, so I suspect it's a memory error.  Should I alias the column names to be shorter (ie the problem is in the text box) or is it a metadata problem?  I'm going home now but tomorrow I will see if splitting the staging table into 4 tables and splitting the conditions into 4 outputs (to be recombined later by an execute SQL command into the real staging table) does what I need.



January 22, 2009 5:54 PM

RVS said:

Hi Andy and Charles,

I thank you for your comments. I still have a few doubts related to handling Deleted columns. I have created a solution to handle all three cases(add,update and delete). I have taken two OLEDB Source(one with source and data and another with destination table's data) then I have SORTED them and MERGED them(with FULL OUTER join) and finally used CONDITIONAL SPLIT to filter New, updated and Deleted data and used the OLEDB Command to do the required action. I am getting Deleted rows by using full outer join.

I am getting expected result with this solution but I think this is not performance efficient as it is using sort, merge etc. I wanted to use Lookup as suggested by Andy. But the solution which you both have given is not fully clear to me. Will it be possible for you to send me a sketch of the proposed solution or explain it a bit in detail?

Charles, regarding no. of comparisons, I don't think it is limited to 19 or 20 because I have used more than 35 comparisons and that is working fine. Please check if you have checked for null columns correctly.

Thanks once again,



January 23, 2009 6:57 AM

Charlie Asbornsen said:

Doh! Thanks Ranvijay.

January 23, 2009 10:01 AM

Charlie Asbornsen said:

Actually what was happening was that since the comparison expression was so long I moved it into WordPad to type it and then copy/pasted into the rather annoyingly non-resizable condition field in the conditional split transformation editor.  It turns out it doesn't like that.  Maybe there were invisible control characters in the string, so I needed to just bite the bullet and type in the textbox.  It works fine now.

It would be nice to have a text visualizer for that field.


January 23, 2009 1:51 PM

vidhya said:

This was the excellent article and Andy illustration style is great.

Thank you

June 30, 2009 9:47 AM

Nostromo said:

Great tutorial!  I'm new to SSIS and I worked through it without a hitch.


July 10, 2009 10:23 AM

DVL said:


Many thanks for the step by step guide.

It's nice to find a way to get your changed and new records in 2 separate outputs. But how who you get the deleted records? The only solution i found is to lookup every PK in the source db table and check if it still excists. If it does it will set the deleted_flag to 1. Do you have any idea to implement the deleted records into your solution? Mine is in a separete dataflow.


August 27, 2009 8:05 AM

CSu said:

Great article! I originally used sort, merge join (with left outer join) and conditional split transforms to perform incremental load. Unfortunately it did not work as expected. Your article has simplified my design and it is now working perfectly. Thanks for sharing. :)

October 26, 2009 7:26 AM

hasan said:

Dear Andy

your solution is great but i have problem. the dimensions are not getting populated with the default data. does this work on the excel source because i have an excel source.

December 29, 2009 7:31 AM

Mike said:


Just read the article, confirms my approach to incremental loading on a series of smallish facts.

I have used the "slowly changing dimension" element in the past to facilitate the same outcome, ie not using type2s (despite being a fact) - but it is much slower.

RVA, re: "I am getting expected result with this solution but I think this is not performance efficient as it is using sort, merge etc"; if the sort(s) are the main problem, you can do the sort on the database and tell SSIS that the set is sorted to avoid using two sort dataflow tasks - not sure if that will give you sufficient gains? The Merge join, as you say, will still be not great within SSIS.

Lastly - has anyone any experience of duplicated KEYS in the source table, that do not (yet) exist in the destination?

I am performing bulk-inserts after the update/insert evaluation. I have a minor concern that if I have a key in the source data, that the FIRST record will correctly INSERT, does the lookup then add this key to memory, so that when the second key arrives it knows to update?

Because, although I do not constrain the destination table, it will cause problems within the data (mini carteseans - *shudder*).

Do I need to be aware of any settings or the like? I am about to do a test-case now - and see what happens...

January 24, 2010 5:35 PM

Mandar said:

Hi Andy,

I want to load data incrementally from source (MySQL 5.2) to SQL Server 2008, using SSIS 2008, based on modified date. Somehow I am not able do it as MySQL doesn't support parameters. Need some help on this.

-regards, mandar

March 15, 2010 6:40 AM

Ramdas said:

Thank you andy for this tutorial. I am using SSIS 2008, the Lookup task interface has changed a little bit, when you click on edit on the lookup task, the opening screen is layed out differently.

March 25, 2010 9:46 AM

KK said:

In my source ID 3 Record has duplicated KEYS so i want first record Insert and Secode Record should be update in Destination table trough SSIS

Can any one help me to resovle this problem.

When I use SCD 2 type when it read record in target the id 3 record is not avlable in target so it’s treat for insert for second record also same.

So that record insert two time I don’t want like that I want to first record insert and scoend record of ID 3 Update.

So any way of resolve this problem .

ID       Name     Date  

1 Kiran 1/1/2010 12:00:00 AM

3 Rama 1/2/2010 12:00:00 AM

2 Dubai 1/2/2010 12:00:00 AM

3 Ramkumar 1/2/2010 12:00:00 AM

March 25, 2010 5:11 PM

Craig said:

I need to incrementally load data from Sybase to SQL.  There will be several hundred million rows.  Will this approach work OK with this scenario?

March 30, 2010 10:45 AM

andyleonard said:

Hi Craig,

  Maybe, but most likely not. This is one design pattern you can start with. I would test this, tweak it, and optimize like crazy to get as much performance out of your server as possible.

:{> Andy

March 30, 2010 10:52 AM

jpedroalmeida said:

Hy there from Portugal,

Andy, i am a starter in SSIS and i found this article very useful and straightforward in explanation with text and images...

Thanks a lot!!


April 25, 2010 11:02 AM

JohnnyReaction said:

Hi Andy

I amended your script to deal with different datatypes (saves a lot of debugging in the Conditional Split Transformation Editor):


This script assists with the creation of the Conditional Split "Changed Rows" condition

-- be sure your results aren't being truncated when you have a table with many columns



USE master


DECLARE @Filter varchar(max)

SET @Filter = ''

SELECT @Filter = @Filter + '((ISNULL(' + c.[name] + ')?'+

CASE WHEN c.system_type_id IN (35,104,167,175,231,239,241) THEN '""'

WHEN c.system_type_id IN (58,61) THEN '(DT_DBTIMESTAMP)"1900-01-01"'


+ ':' + c.[name] + ')!=(ISNULL(Dest_' + c.[name] + ')?' +

CASE WHEN c.system_type_id IN (35,104,167,175,231,239,241) THEN '""'

WHEN c.system_type_id IN (58,61) THEN '(DT_DBTIMESTAMP)"1900-01-01"'


+':Dest_' + c.[name] + ')) || '

FROM sys.tables t

INNER JOIN sys.columns c

ON t.[object_id] = c.[object_id]

WHERE SCHEMA_NAME( t.[schema_id] ) = 'dbo'

AND t.[name] = 'DimUPRTable'

AND c.[is_identity] = 0

AND c.[is_rowguidcol] = 0



SET @Filter = LEFT(@Filter, (LEN(@Filter) - 2))

SELECT @Filter


-- c.*


-- sys.tables t


-- sys.columns c

-- ON t.[object_id] = c.[object_id]


-- SCHEMA_NAME( t.[schema_id] ) = 'dbo'

--AND t.[name] = 'DimUPRTable'

--AND c.[is_identity] = 0

--AND c.[is_rowguidcol] = 0




-- AS [Schema]

-- , AS [Table]

-- , AS [Column]

-- ,CASE WHEN columns.system_type_id = 34    

-- THEN 'byte[]'            

-- WHEN columns.system_type_id = 35    

-- THEN 'string'            

-- WHEN columns.system_type_id = 36    

-- THEN 'System.Guid'            

-- WHEN columns.system_type_id = 48    

-- THEN 'byte'            

-- WHEN columns.system_type_id = 52    

-- THEN 'short'            

-- WHEN columns.system_type_id = 56    

-- THEN 'int'            

-- WHEN columns.system_type_id = 58    

-- THEN 'System.DateTime'            

-- WHEN columns.system_type_id = 59    

-- THEN 'float'            

-- WHEN columns.system_type_id = 60    

-- THEN 'decimal'            

-- WHEN columns.system_type_id = 61    

-- THEN 'System.DateTime'            

-- WHEN columns.system_type_id = 62    

-- THEN 'double'            

-- WHEN columns.system_type_id = 98    

-- THEN 'object'            

-- WHEN columns.system_type_id = 99    

-- THEN 'string'            

-- WHEN columns.system_type_id = 104  

-- THEN 'bool'            

-- WHEN columns.system_type_id = 106  

-- THEN 'decimal'            

-- WHEN columns.system_type_id = 108  

-- THEN 'decimal'            

-- WHEN columns.system_type_id = 122  

-- THEN 'decimal'            

-- WHEN columns.system_type_id = 127  

-- THEN 'long'            

-- WHEN columns.system_type_id = 165  

-- THEN 'byte[]'            

-- WHEN columns.system_type_id = 167  

-- THEN 'string'            

-- WHEN columns.system_type_id = 173  

-- THEN 'byte[]'            

-- WHEN columns.system_type_id = 175  

-- THEN 'string'            

-- WHEN columns.system_type_id = 189  

-- THEN 'long'            

-- WHEN columns.system_type_id = 231  

-- THEN 'string'            

-- WHEN columns.system_type_id = 239  

-- THEN 'string'            

-- WHEN columns.system_type_id = 241  

-- THEN 'string'            

-- WHEN columns.system_type_id = 241  

-- THEN 'string'        

-- END AS [Type]

-- ,columns.is_nullable AS [Nullable]


-- sys.tables tables    


-- sys.schemas schemas

--ON (tables.schema_id = schemas.schema_id )    


-- sys.columns columns

--ON (columns.object_id = tables.object_id)  


-- <> 'sysdiagrams'

-- AND <> 'dtproperties'


-- [Schema]

-- ,[Table]

-- ,[Column]

-- ,[Type]

July 28, 2010 8:26 AM

Paul Klotka said:

Using T-SQL to do change detection.

I would not use a join to detect change because in the where clause you need to handle NULL values. For example if ColA in Source is NULL it doesn't matter what ColA is in the destination, the where clause will return false and not detect the change.

To get around this I use a union to detect change. Here is an example.

select ColId, ColA, ColB, ColC from Source


select ColId, ColA, ColB, ColC from Dest

This returns a distinct set of rows, including handling NULL values. All that is left is to determine if the ColId appears more than once in the set.

select ColId from (

select ColId, ColA, ColB, ColC from Source


select ColId, ColA, ColB, ColC from Dest

) x

group by ColId

having count(*) > 1

Now I have a list of keys which changed. I can take this list and sort it to use in a merge join in SSIS or I can use it as a subquery to join back to the Source table. See below.

select ColId, ColA, ColB, ColC from Source s

inner join (

select ColId from (

select ColId, ColA, ColB, ColC from Source


select ColId, ColA, ColB, ColC from Dest

) x

group by ColId

having count(*) > 1

) y

on s.ColId = y.ColId

July 28, 2010 2:06 PM

Chhavi said:

Thanks for the good explanation and screenshots. I found this website to be extremly helpful and supportive.

Please let me know if I can learn something more from you and rest of the guys visiting this website, so that we can become better in SSIS and SQL server 2005 or 2008.

Please provide us similar articles so that we can through them and practice.

Thanks again Andy.

Long Live Andy :)

August 18, 2010 3:59 PM

AP said:

This is excellent Article ! Great job

October 11, 2010 10:12 AM

TheAviator said:

Thank you very very much. No where on the net I found it explained in so detail and clear. Thanks again

October 22, 2010 11:08 AM

V said:

My requirement is

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

The above code, perform only Insert and Update, however, it doesnt Delete data from destination table which has been deleted from Source data.

I would not like to perform Truncate\Delete ALL data from destination table.

Please let me know how shall i do this.

Basically, it should perform Update, Insert, Delete in one single package (task)

December 20, 2010 6:39 PM

Dpostman said:

Probably not the most common scenario, but if your source and destinations are coming from sql server could you just select checksum(*) as a column from your source and destination tables and test it to determine if the row has been updated? I would think it would be a pretty safe alternative when you have a lot of columns.  

Or has anyone created a hashing formula in an expression?  (or would that be too slow to consider?)

January 17, 2011 5:16 PM

dbraver said:

The following new feature of 2008 R2 seems does the same

February 16, 2011 7:28 PM

Peter Schott said:

Merge may be able to handle this, but if you start working with millions of rows, it tends not to perform too well.  For smaller data sets, it's pretty effective, but I've seen that command sit for a while as Merge tries to calculate the updates/inserts.

April 20, 2011 4:52 PM

Samit Shah said:

Hi Andy,

Its great article. Very well explained. But I was wondering I am having around 20-30 tables in mysql and I have to use SSIS package for moving data of this tables to sql server. Some of the tables has 40-50 million records. I have to do this load very frequently (might be daily), is this best approach for it or if you can suggest some better approach.

April 25, 2011 8:44 AM

andyleonard said:

Change Detection is a topic of many design patterns. Here I used a rather brute-force method for detecting updates, mainly to demonstrate the concept. There are more methods for detecting changed rows and I hope to blog about some soon.

Thanks for the comments!


April 25, 2011 7:33 PM

Kal said:

This works great!

Now the issue is, I have more than 100 tables which need an incremental load..

Do i have to build 100 packages? or is there an easy way out?

Please Help..

May 12, 2011 6:24 AM

Thiru-BI said:

i just want to load based on the date ie if our table have date column we will capture that maximum date based on the loading if again running our package it allows which records have date greater than already capturing date. Allow this records only by the we can get only new records to target.

can you please some idea to achive this?

Thanks & Regards,

Thirunavukkarasu P

May 30, 2011 3:33 AM

Michael Baumanns said:

Hi Kal,

i have actually the same problem. Maybe you can use the Slowly Changing Dimension Tool. This generates Updaten and insert command for you.

May 30, 2011 9:50 AM

Romualdo said:

Hi Andy!

Thanks a lot for that topic.

I'm starting to use SSIS and i have some doubts:

a) If my Destionation Database is null, i got error. Don't insert the rows.

b) What can i do to delete rows on destination database who not exists on source database?

c)If i have come source column with null, don't update the destination. But that i understant why a i see your sugest:

(ISNULL(Dest_ColA) ? -1 : Dest_ColA) != (ISNULL(ColA) ? -1 : ColA)

July 20, 2011 2:10 PM

Lavanya said:

Thank u a lot...nice explanation

July 26, 2011 9:00 AM

J Channin said:

Could you please explain further....

"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."

Thank you

August 4, 2011 3:03 PM

D Sharma said:

Experts, i have a question.I'm working on loading a Very large table having existing data of the order of 150 million records which will keep on growing by adding 1 million records on a daily basis.Few days back the ETL started failing even after running for 24 hrs.In the DFT, we have source query pulling 1 million records which is LOOKed UP against the Destination table having 150 million records to check for new records.It is failing as the LOOKUP cannot hold data for 150 million records.i have tried changing the LOOKUP to Merge Join without success.Can you please suggest alternative designs to load the data in the large table successfully.Moreover, there is no way i can reduce the size of destination table.i  already have indexes on all required columns.Hope i'm clear in explaining the scenario.

August 17, 2011 10:47 AM

andyleonard said:

Hi D,

  You can limit the number of rows used by the Lookup by using a SQL query as the Lookup source instead of the entire table.

Hope this helps,


August 17, 2011 11:11 AM

D Sharma said:

Thanks for the reply Andy.I have already mentioned that it's not possible for me to actually reduce the size of the LOOKUP Query since i need to check existing rows which can be anywhere in the table.Something came to my mind just now on which i would like expert comments.I'm thinking about splitting the target table into parts with sql query and use it to join with source sql query to find possible newer records which would be joined with the various parts of the destination table one by one to get to the actual new records.Will try that at work tomorrow, just came to mind now.Would appreciate a lot if you can suggest any better alternative.

August 17, 2011 12:03 PM

andyleonard said:

Hi D,

  I recommend identifying the rows in the large table before you reach the lookup, and staging the data you need to return from the lookup - along with the lookup-matching criteria - in another table. Truncate this table prior to loading it. Populate it. Then use it for the lookup operation. Kimball refers to this a "key staging".

Hope this helps,


August 17, 2011 12:44 PM

dilip said:

Hi andy,

I tried this article in BIDS SSIS 2008 R2

but every time it had to to update it wont do any update instead it inserts a new row. As given in above example when ColID =1 it needs to update ColA but instead it inserts new colA with same Colid=1 so it has now two rows with Colid=1 one with cola = b and other with cola =c:( any idea where i went wrong....i followed the same steps which have been given by you.



August 19, 2011 3:44 PM

Neha said:

Hi Andy, Very nice article. I had another question though, how do you handle data deletes. I see that the new records are inserted and changed ones are updated. What about the records that were deleted?



September 20, 2011 10:05 PM

Jessica said:

Hi Andy, Awesome posts.

I have one question not sure if this is the right place. I am working on a data warehouse load that has comprises of multiple SSIS packages and my challenge is to make it rerunnable. Each package calls a stored procedure which is rerunnable i am trying to add something like a Batchid to each run. any ideas on how this should be approached?


September 27, 2011 4:23 PM

andyleonard said:

Hi Jessica,

  I wrote a post recently about designing an SSIS Framework. It can help. There are other potential gotchas with DW loads, so please hit the email link in the upper right of this page. We'll. Probably do more good taking this offline.


September 27, 2011 5:29 PM

srinivasan M said:

I am the beginner for SSIS packages . It is very useful

Thanks a lot

October 4, 2011 1:12 AM

indy said:

Hi Andy,

I followed the article published today - level 4

I know you are going to publish article to take care of deletes as well. But I need to implement this in my project by this week. Do you have deletes article handy? Also, I neeed to work with 40 + tables and the SSIS package should refresh the destination database every 8 hours. How do I manage 40+ tables and data resfresh every 8 hours. Can you please suggest me better solution to achieve this?

October 12, 2011 1:32 PM

andyleonard said:

Hi Indy,

  Please email me at



October 12, 2011 8:01 PM

Amu said:

This OleDB command will be slow for lakh number of records..

By loading the data into stage table and update it outside the dataflow using execute sql task is one option....

Any other option is available to improve the performance of the package.

November 10, 2011 5:46 AM

andyleonard said:

Hi Amu,

  You are correct and thank you for pointing this out. I have written another series about SSIS Incremental Loads for I cover your suggestion in Step 4 of the Stairway to Integration Services (


November 10, 2011 9:16 AM

malli said:

is there any chance.

If source file is deleted, it need to effect on destination file

how can we use condition for that in conditional split,

the above ssis package is gud ( i am working wth it )

but the thing is it cant handle the deleted tables

can u help me with that

November 10, 2011 12:25 PM

malli said:

Sorry i mean

does it able to handel the deleted rows ( not columns sorry for that ) in source table it shouls effect the destination to

by the way i am using sorce as oracle data base

November 10, 2011 2:53 PM

andyleonard said:

Hi Mali,

  Check out Step 5 of the Stairway to Integration Services - it talks about Deletes:

Hope this helps,


November 10, 2011 3:17 PM

Reddy said:

Hi Andy,

if a cloumn has null values

condition split saying "The expression results must be Boolean for a Conditional Split"

my error is

[Conditional Split [127]] Error: The expression "(STATUS != L_STATUS) || (ORDER != L_ORDER)"on "output "Update" (167)" evaluated to NULL, but the "component "Conditional Split" (127)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row).  The expression results must be Boolean for a Conditional Split.  A NULL expression result is an error.

November 11, 2011 10:17 AM

Malli said:

Hi Andy,

Thanks a lot for that,

right now i am facing a problem that my records r some millions

so its is geeting error as out of memory some thing so on

is there any suggesion on that



November 11, 2011 5:23 PM

Reddy said:

I got it

anyway thanks for your post which u have posted recentelly.



November 11, 2011 5:25 PM

malli said:

Hi Andy

The error is

Error: The system reports 89 percent memory load. There are 3477643264 bytes of physical memory with 357049128 bytes free. There are 2147352579 bytes of virtual memory with 97837956 bytes free. The paging file has 5452554249 bytes with 1323617112 bytes free.

November 11, 2011 5:27 PM

Hi Andy, said:

I am attempting to incrementally update from a table with 170 million rows with 78 fields. The Lookup Task failed when I attempted to use it like you described above due to a Duplicate Key error...cache is full. However it is now extremely slow. Do you have any dvice on tweaking the lookup task for my environment?

my source is from Oracle

Is there a way to speed things up/replace the lookup task by using a SQL Execution Task which calls a left outer join?

Thanks Again

November 15, 2011 9:15 PM

andyleonard said:

You should consider using a query in the Lookup Transformation and not selecting the table name from a dropdown. Selecting the table name essentially attempts to load the entire table into RAM before the data flow executes. Limiting the rows and columns returned will shrink the data volume returned.

You can also look into key-staging. There's mention of it here ( in the section on Targeted Staging.

Another pattern to consider is Range-based lookups (

Hope this helps,


November 15, 2011 11:56 PM

John said:

Hi Andy,

This article has been veryuseful as I have built a package that almost works... ;-)

It actually works for 4 out of 5 tables.  The 5th table actually has a 5 field combined primary key.

So in the Conditional Split

--New rows, I put the following:

Isnull(Dest_field1) && Isnull(Dest_field2) && Isnull(Dest_field3) && Isnull(Dest_field4) && Isnull(Dest_field5)

which are the 5 fields that make the primary key.

and in the -- Changed Rows, I put the following  

(Field1 == Dest_field1) && (Field2 == Dest_field2) && (Field3 == Dest_field3) && (Field4 == Dest_field4) && (Field5 == Dest_field5) && (TimeStamp != Dest_TimeStamp)

which to me is just simply logical.

The problem I'm having is that I get a insert Primary key violation... for which I'm not too sure how to troubleshoot since I only know the basics.

What do you think I'm doing wrong?

December 21, 2011 10:19 AM

andyleonard said:

Hi John,

  You want to use the Lookup Transformation to manage mapping your primary key fields. Remember, the Columns tab of the Lookup is akin to setting the JOIN ON clause. If you were joining this table in T-SQL, you would include all five fields.

Hope this helps,


December 21, 2011 10:39 AM

John said:

Hi again Andy,

Thank you for the pointer... It actually helped me understand and fix my problem.

I had all columns chosen for the join in the Lookup transformation.  By just joining  the keys and it helped me understand the conditional split conditions.

All I need now is to learn more about debugging and error handling which is still a big blur for me...

Thanks again! Great blog! :-)

December 21, 2011 12:02 PM

Kingdom said:

Thank you very much AndyLeonard, this is very helpful.

Many thanks

February 1, 2012 9:15 AM

Samit Shah said:

Hi Andy,

Could you please advise how to do incremental load for 300+ tables, as it would be difficult to create dataflow for all tables and I have the requirement to use SSIS along with logging of how many records are inserted/updated. Could please help me out on the approach to be taken through SSIS.

Thanks and Regards,

Samit Shah

February 3, 2012 4:19 PM

andyleonard said:

Hi Sanity,

  A couple options come to mind. 1. Use. Net to generate and save the packages. 2. Use BIML (

Hope this helps,


February 3, 2012 6:43 PM

Samit Shah said:

Hi Andy

Thanks for the help. I was able to create the packages using BIMLScript.

Thanks and Regards,

Samit Shah

February 9, 2012 6:11 PM

Hui Shi said:

hi andy , great article. However, I have two questions

1. When you detect new rows, you used left out join the source table

to the target table and find all records where columns from right tables are null. That's fine but what about if source and target tables contains billion records? Is that applicable?

 I know the other alternative might be extracting the max(modifiled_date) from the target table and get data from source where date is greater than max(modified_date), what about if there are no such audit columns on the source and target tables?



March 19, 2012 2:24 PM

Peter Schott said:


 In those cases, you are probably better off using some sort of max(Created/Modified Date) - with an index!  If you're replicating that table, it could potentially be added as a computed column on the replicated side with the index created there.  That would let you know what records are new/changed since the last run.  Pull those values into some form of staging table and compare against that - it will likely perform better that way than doing a direct join.

March 19, 2012 3:10 PM

Hui Shi said:

thanks peter. do you know any way to add audit columns as a computed column on the replicated side? In my case, our source table did not

contain such audit columns and in order to add that one, seems I need

to write a trigger so that a timestamp will be inserted to each record whenever there is a transaction? Do you have any suggestions?

March 19, 2012 3:54 PM

Peter Schott said:

Hui, that's where you start running into issues. If you don't have something in your row that's tracking Created/Updated Dates, you'll need to add it and possibly a trigger as well.  Created Date is pretty straightforward to default.  Updated Date would need an AFTER UPDATE trigger to populate it. I think it could still be done on your replicated side, but in this case, you're better off adding it to your main source database. You'll likely want it at some point even if you don't think you'll need it.

If the data is strictly INSERT, you can get the max PK value (or some other unique value) and run against that.  You have the option of Service Broker as well, but that takes some work and probably more triggers to manage.  If you're on SQL 2008 Enterprise, CDC may be an option.

In our case, we have Created/Updated Dates (some NULL) and were able to create a computed column to show COALESCE(Updated, Created, '') as WarehouseLoadDate. We could then index that and find just the rows that were new/changed. From there we processed as appropriate.

March 19, 2012 4:58 PM

jim said:

hi andy

i have a project where i need to load date each time for only 2 month

and every year delete the last month and than reload with the next month. i'm having trouble with that i would be great if you could help me out with some ideas



July 16, 2012 2:47 AM

Eliana said:

Hi andy

Can I replace the you source DB by a Excel file?  



July 16, 2012 9:28 PM

andyleonard said:

Hi Eliana,

  Yep, but there are a limited number of SSIS data types available from Excel (8, if memory serves) and mapping them to SQL Server data types can be "tricky".


July 17, 2012 4:57 PM

Eliana said:

Hi Andy

Yepp I got a lot of headaches when I try to format import excel files to DB using SSIS solutions . Do you know some trickies for that?  

Now I'm trying to testing your solution but using 2 tables (1 source and 1 destination table) in the same DB

I'm imported the excel file to mytable_source (tmp) and I made a lookup and split from here to update and insert new records into mytable_dest. But I'm still having issues... Mytable_Source have the same structure like Mytable_Source, even the Increment ID, but I want to use other fields to determine if the records are new or update. But no records are inserted and changed

Any idea?

July 18, 2012 12:36 AM

andyleonard said:

Hi Eliana,

  You may want to take a look at another series of articles I wrote about Incremental Loads. It provides more detail and screenshots. You can find the series at

Hope this helps,


July 18, 2012 12:55 AM

Eliana said:

Hi Andy,

I've an issue and I don't know what I'm doing wrong!!!

1. I have a SOURCE table with records and an empty DEST table.

2. I want to use 2 fields for a lookup, OrderDate and OrderNo

3. I choose only these in available lookup columns (join) and I used DES_ in the Output alias

4. In the conditional Split I'm using the following contidions

4.1 New Rows --> (OrderDate  != DEST_OrderDate  && OrderNo != DEST_OrderNo)

4.2 Change Rows --> (OrderDate  == DEST_OrderDate  && OrderNo == DEST_OrderNo)

but When I ran any new rows has been inserted

what I'm doing wrong?



July 24, 2012 10:05 PM

andyleonard said:

Hi Eliana,

  You detect new rows by checking for a NULL (using the SSIS IsNull function) on any column returned from the Destination in the Lookup transformation. You use the expression currently in your New Rows detection to detect Changed Rows.

Hope this helps,


July 25, 2012 10:08 AM

Eliana said:

Thanks for the help, it's working now



July 25, 2012 7:34 PM

andyleonard said:

Good job, Eliana!


July 25, 2012 8:07 PM

Eliana said:

Hi Andy,

I hope you can help me with this new issue

Now I have a solution perfectly running separately but. If I want to run all together that is stuck in the validation phase of my second task.

My solution have 3 stages

1. Import excel file to my source table (53647 rows)

2. Insert/update my dest table, from source table (using lookup and conditional split)

3. truncate source table.

First stage is ok

Second is stuck in a  SSIS.Pipeline: Validation phase is beginning. showing yellow color in the Data flow but doing nothing.

I setup Delay Validation as a True in each DataFlow but this is ignore I guess.

If I ran each Dataflow separatelly that ran oh and  faster

What I have to do to improve it?



July 25, 2012 10:42 PM

Sarika said:

Hello Andy,

Your article has been very helpful.



July 30, 2012 3:26 PM

Yogesh M said:

Great Artical...Thanks sir

October 19, 2012 3:34 AM

Ron said:

hi Andy,

You said: You can limit the number of rows used by the Lookup by using a SQL query as the Lookup source instead of the entire table.

Is it possible to dynamically pass a value from the destination table to a where clause in such a query?  So I can, for example, fine max(ID) in the destination, and only get rows from the source that are greater than that (meaning only new rows)?


February 11, 2013 8:07 PM

andyleonard said:

Hi Ron,

  In SSIS 2008, yes you can. The Lookup Query is exposed in the Expressions for the Data Flow containing the lookup transformation.

Hope this helps,


February 22, 2013 1:09 PM

srinivas said:

Hi friends,

         in increment loading i have small doubt.please clarify me.

here we are useing oledb command for updateding records.

without useing oledb command is it possible or not to update records.

that time is it possible to  use execute sql how to we use in control flow level. i follow same query to write in execute sql task. and mapping paramers variable.i write query in execute sql task like

update table  set  name=? ,


           where   id=?

and  i map parmerts mapping.

but its no updated any record.

i want achive same result  useing execute sql task.plese tell me wha steps i need follw.

March 24, 2013 10:27 AM

rohit said:

thanks i does not clear tooo much can you provide the video  that would help me lot.  thanks thanks thanks  in advance

with regards


March 25, 2013 5:50 AM

Daniel Macho said:

I recommend you take a look to the Dimension Merge SCD component and the video set from youtube (specially the 6th one)

April 3, 2013 10:45 AM

Akshay said:

New to SSIS ?  Andy Leonard is the name to remember. Article of July 2007 still getting appreciated & lauded , Updated in sqlservercentral to date.

Thank you very much ANDY for sharing your knowledge.  

April 13, 2013 2:27 PM

Denis Goch said:


Thank you very much for this contribution. I searched a lot for something like this and none of the things I found, worked like yours. I was trying to import a XML file into a SQL Server 2008 and your example worked just perfectly. Thanks again.

April 15, 2013 9:38 PM

Austine said:

Thank you so much, am a baby in this hence my question, can I use same to do my incremental load on my fact table? what if I don't want to use the conditional split but just want to insert , update in the destination?

April 17, 2013 4:20 PM

Pratik said:

Hi andy .

How can I go for Update and Delete with this SSIS package.


May 30, 2013 1:03 PM

Saint said:

Thanks Andy, your article gave me a head on on the job.  

July 1, 2013 2:58 AM

gibs said:

Hi Andy,

I am a beginner with ssis packages..currently im working with sql server 2008 database. I have to implement an ssis package which should take data from oracle database to sql db. I have successfully implemented it, but the issue is that it is not checking 'new data' in oracle...its just appending all the rows in source table to destination table. so im facing data duplication issue. Could you please help me to solve this? i just wan that, my ssis package should take only 'new data' from oracle and insert it to sql.

Thanks in advance,

September 14, 2013 2:12 AM

andyleonard said:

Hi Gibs,

  The scenario you describe is addressed by implementing an Incremental Load design pattern. This very post will solve the issue of duplicated rows appended to the destination table.

  You may also want to check out my series at SQL Server Central: Stairway to Integration Services ( In it, I describe the Incremental Load Design Pattern in more detail.

Hope this helps,


September 14, 2013 7:04 AM

venkat said:

Hi andy,

i need to create one ssis package

in Prodserver i have 200 tables with 6 schemas.

I need to initial load and Incremental load  all these 200  tables

using ssis pkg from prod server to sandbox.

shall i need to create 1 pkg for each table for all 200 tables? or

Please help me how i can proceed.

Thanks in advance


October 6, 2013 1:17 PM

Irit said:

Hi Andy,

May be you will help me with my issue. I need to load .xls files, but my files are builed so that i cannot load every column till it's ending, but i need to stop loading in the specific row.

That means, if i have, for example, columns with 50 rows, there are only 10 rows to be loaded.

How can i stop the loading exactly on the 10th row for each column?

Is that foreach container? conditional split?

Thanks very much

October 29, 2013 5:17 AM

andyleonard said:

Hi Irit,

  One way is to add a Script Component. In .Net, you can count the rows flowing through the buffer and only output rows while the row count is less than or equal to some value.

Hope this helps,


October 29, 2013 5:26 AM

Irit said:

Andy, the problem that the row number is changing all the time, but i have the blank row after the rows, which have to be loaded, so maybe this can help us to stop the loading till arriving to the blank row?

October 29, 2013 5:40 AM

andyleonard said:

Hi Irit,

  You could use a Script Component to detect the blank row. If the blank row is consistent and the number of rows varies, that's the approach I would take.

Hope this helps,


October 29, 2013 7:36 AM

andrismg said:

Hi Andy, Excellent article and thanks a million, Sir Andy, this help me a lot, but no all isn't color Rose, lol. I implementet this project, with my environment; my big problem, I think is the lookup flow. When execute my project, the space size in HardDisc "C:\" disappears, Can you tell me why occurs it?

November 11, 2013 2:47 PM

Cesar Escobar said:

Excellent post, very clear.

Nice Work

February 6, 2014 4:56 PM

Tina said:

It was very useful post

May 2, 2014 1:02 PM

SenthilKumar said:

Very good guidlines

May 14, 2014 8:00 AM

Hannah said:

Hi Andy and all,

I have been using your example for developing some packages.  For most of them, it is working beautifully.  There is just one package which some fields contains NULL look up values. In this case, the 'changed rows' condition evalutes to NULL and gives error messages saying the condition should evaluates to a boolean value.   I got around it by setting 'ignore failures' in the condition split component.  Next I found, the update of the changed rows still evaluate to NULL as long as the lookup table contains NULL values in those rows.  Can you suggest what I need to do in this case?  I hope I explained my issues clearly.  If not, please let me know. thank you so much.  

June 13, 2014 2:41 PM

Hannah said:

Hi Andy,

I sent you a question earlier.  Did you get it and can you help? It is urgent as it may be true to everyone who has a question. thank you

June 13, 2014 4:48 PM

andyleonard said:

Hi Hannah,

  I appreciate you reading and commenting on this blog. I try to be responsive to questions from readers but sometimes I'm engaged in my day job and cannot respond as quickly as readers would like. Other times I am partially or completely disconnected from the internet for days or weeks. I recently returned from a trip out of the country, for example, and was offline much of that time. I know authors of many popular blogs and many of them operate in a similar fashion.

  I've written a series at SQL Server Central called the Stairway to Integration Services ( The fourth article in the series ( provides a working description of using the SSIS Expression Language IsNull function in a Change Detection expression.

Hope this helps,


June 13, 2014 9:55 PM

Niraj said:

Really very Useful article .... thanks Mr.Andy

July 10, 2014 8:52 AM

Nirav said:

Will this work for large data sets?

September 25, 2014 10:23 AM

Vijai Chaubey said:

Hi Andy

Really very Useful article. Thanks for the help.

Thank you very much Andy

November 27, 2014 1:44 AM

prasad said:

Hi Andy ,

I followed the same steps given by u , if i update any row in source table it is going as a new row for destination table and not getting updated

December 16, 2014 8:14 AM

SivaPriya A said:

clarity on the content is more appreciated.Thanks for making it simple to understand

March 19, 2015 2:24 AM

Ashish Mandal said:

Thank you for presenting the article in very simple way. It was really to understand .:)

March 24, 2015 8:09 AM

Gus said:

Hi Andy,

great Article. It's good to see something that was started in 2007 still going :)

I am after a simple solution but at the same time it shows itself hard to find. I am looking for slowing changing dimensions/delta type of solution but instead of done per table which is what SSIS is based on I would like to be able to have a configuration table specifying the metadata which will be used by the package. Do you know of any solution which can implement this? would you recommend any?



June 2, 2015 11:12 PM

Phong Le Ngoc Ky said:

It'd be better if we can use Hashbytes column on Conditional Splitting for 'Changed Rows'.

June 11, 2015 12:11 PM

Tom said:

Hi Andy,

thanks for your great work.

I just have, hopefully, a simple problem. I have the errorcode -1071607764, which tells me "The expression evaluated to NULL, but a Boolean result is required. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error." after the conditional split.

Unfortunately I have no clue at the moment why this error occurs..

Do you may have an idea?

Thanks in advance.

Cheers Tom

June 12, 2015 10:59 AM

andyleonard said:

Hi Tom,

  You're getting that error because one of the values in a column used in the expression is NULL.

  I've written a series at SQL Server Central called the Stairway to Integration Services ( The fourth article in the series ( provides a working description of using the SSIS Expression Language IsNull function in a Change Detection expression.

Hope this helps,


June 13, 2015 9:42 AM

Phong Le Ngoc Ky said:

Hi Tom.

I faced to your same error. To overcome this, I use ISNULL expression to identify those NULL values and change to 'space' character to ensure the correct equal expression in Conditional Split.

@andy: is it the same with your solution or do we have other better solution on this issue?

Thanks a lot,

Phong Le Ngoc Ky

June 15, 2015 3:41 AM

Tom said:

Hi Andy,

thank's a lot for your help :)

I've found the mistake and my process is working now.

The Stairway to Integration Services is also awesome.

Kind regards


June 15, 2015 8:30 AM

AM96 said:

I have a fact table which has about 27 million records and i am trying to update the fact table so i don't have to fully process the cube every day. The pattern i am following is using look ups to verify whether it an existing record or a new record. I am using SSIS as the etl tool. I am wondering what would be the best way to handle it. Should i load the Source system key from the fact in a cache look up and then compare or try to follow something else.

Any response or help would be really appreciated.

July 27, 2015 10:09 PM

Satish Kumar said:

Very nice article and thank you so much Andy !

Satish Kumar

August 26, 2015 9:12 AM

Sekhar said:

"insert bulk failed due to a schema change of the target table" while executing Incremental packages in production environment.

Some of the records inserted successfully but in the middle i faced this problem

and when i execute in local everything working fine with all success

October 15, 2015 7:20 AM


I am loading data from sql server to oracle as incremental load and always data loading into new rows because in the look up tranformation ,data is showing null even data is available in destination table.

Can u pls advice.


November 10, 2015 2:08 PM

Guest said:

I faced the same problem as you DBA-HELPER when I deal with Oracle related SSIS. For loading from Oracle to SQL, after changing the lookup from Full Cache to Partial Cache the package executed correctly when new rows or updates are added.

However, the same issue couldn't be solved when I load data from SQL to Oracle.

Anybody got any insights or did DBA-HELPER solved it? Please help me :(

February 4, 2016 1:53 AM

Maria said:

just want to say this incremental load is complete and really useful for me. Thank you!

May 31, 2016 11:36 AM

Emad Hanna said:

Hi. I have a QQ. I have the source table in SQL Server Database and the Target Table is in Oracle Database. And I'm dealing with in average 140000 records. Which is the best way to do the incremental load with two different databases used. This is an urgent request. Also, with the date field in SQL Server yyyy-mm-dd and in oracle is dd-MON-yy. How can I use that date field in both for update task in the conditional conflict task.

July 27, 2016 7:17 PM

Cyanide Lancer said:

Cannot believe how useful is this page even in 2016!

November 4, 2016 5:52 AM

Jitendrasinh Makwana said:

how to create query...

Sample table : Transact

Entry    Date               PartyIdno    Catage    Credit/Debit  Amount

------   ----------             ----------        ---------     ------------         --------

    1   02-01-2016          1           Receipt         C                8,200

    1   02-01-2016          5           Payment        D               8,200

    2   14-02-2016          1           Sales             D            11,200

    2   14-02-2016          4           Sales             C              6,500

    2   14-02-2016          2           Sales             C              4,700



Entry       Date       PartyIdno                              Debit  Credit    Balance

-----       ------     ----------                          -----  -------   -----------                  

                                    SubId  SubAmount

                                    -----  ---------

 1       02-01-2016      1                                           8,200    8,200 Cr

         02-01-2016                   5     8,200 Dr

 2       14-02-2016      1                                   11,200           3,000 Dr

         14-02-2016                   4     6,500 Cr

         14-02-2016                   2     4,700 Cr

SQL Statement :



ON 1 = 0 WHERE T1.PARTYIDNO=1 OR T2.PARTYIDNO&lt;>-1  ", Connection)

Dim da As New SqlDataAdapter(cmd)

Dim dt As New DataTable


DataGridView1.DataSource = dt


March 29, 2017 6:27 AM

kelvin said:

HI  After this step "

UPDATE dbo.tblDest


ColA = ?

,ColB = ?

,ColC = ?

WHERE ColID = ? "

i did not understand. How to set up parameter to param 0 param 02?

do i need to write this code ?

UPDATE SSISIncrementalLoad_Dest.dbo.tblDest


ColA = SSISIncrementalLoad_Source.dbo.ColA

,ColB = SSISIncrementalLoad_Source.dbo.ColB

,ColC = SSISIncrementalLoad_Source.dbo.ColC

WHERE ColID = SSISIncrementalLoad_Source.dbo.ColID

October 14, 2017 10:41 PM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement