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 - ETL Instrumentation, Part 3

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

Published Sunday, November 18, 2007 11:45 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



Andy Leonard said:

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

December 26, 2007 2:56 AM

Shreedhar said:

You saved me a hell lot of time. Very very valuable information. Thanks for sharing this.

August 5, 2009 5:33 AM

Steve said:

Andy,you are a very good instructor. Your SSIS Design Pattern - ETL Instrumentation is well crafted. I have started on leason III. Before I get distracted I want to make sure I thank you for sharing your method of building SSIS packages.

Keep it up!


October 7, 2010 3:26 PM

Omikhan said:

Andy, you did a wonderful job here. It helped me a lot. Thanks

April 6, 2012 2:29 PM

Chris said:

Hi Andy,

I reproduced every part of series and it helped my a lot. Thanks.

A little suggestion: most of the sql code is text, so I could easyly copy & paste it. Some code is inserted as a screenshot ('Some Optimization...'). It would be nice if every (sql) code is inserted as text :)

So, I check for other posts from you :-)

July 18, 2012 9:52 AM

Paul said:

Hi Andy,

Great Tutorials! Thanks for your work and the time spent in it.

I have one question regarding the number of columns to be updated. With the proposed approach to determine whether an update should be performed all the columns have to be compared one by one. For a table with 10 or more columns (imagine a table with 30 or 40 columns) this approach is too expensive. Can you recommend me another approach for these cases?

Any comment will be appreciated.

Kind Regards

July 25, 2012 3:16 AM

andyleonard said:

Hi Paul,

  Yes. You can use the HashBytes SQL Server function to create a single binary value that represents a hash of the values in the row. Comparing the hash on the source with the hash on the destination (I recommend using a computed column in the destination table), you can quickly determine if any value has changed. I need to blog about this...


July 25, 2012 10:57 AM

Edos said:

Beautiful! Thank you Andy. Truly, you're putting in the faces of millions all around the world. Keep on the good work sir.

I was wondering if you still blog about the HashBytes Sql Server function you talked about? If yes, could you please share the link on this post so we can follow along. Thank you

July 23, 2013 12:58 AM

VladK said:

Andy, why we need fields with prefix 'Dest_' in stage table stgContactChangedRows ?

September 16, 2015 9:52 AM

andyleonard said:

Hi Vlad,

  If your field names are the same in the source and destination, SSIS will pick one of the fields and append " (1)" to the name. I hate that. I would rather alias the column names using a prefix like "Dest_".

Hope this helps,


September 16, 2015 2:03 PM

VladK said:

Andy, I mean we do not use 'Dest_%' fields in the next update statement. So we could not map them to destination (stgContactChangedRows )  at all. The only reason for that the 'New Table' wizard creates them automatically ?

September 18, 2015 10:44 AM

manu said:

if 100 columns having one table

here update one or two columns and insert new one record

with out comparing all how can we pass the destination .

May 23, 2017 2:40 AM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement