Advanced Debugging in SSIS no Script Task Related

http://www.techbrothersit.com/2013/12/ssis-how-to-watch-value-of-variables.html

I wanted to just have this out there.  More of a re-blog because it needs to be pushed up.  This is how you do this properly when you are having an issue and are unsure of the exact value of a record. I also tend to log every variable using a script task as well during run time so that I can review the ssis logs for it.

In Place Updates of Two Fields To Each Other in SQL

Not really sure how to classify this, but there are cases when you want to do an update of data in your system where you update one field to another field and set the other field to NULL.  Those of us that have had to approach this problem before know what the answer is, but I decided to create an example.

So the situation is this:  I have a table with two columns, and I want the first column to be overridden by the second column, and the second column to be set to NULL, if and ONLY if the second column is NOT null.  

The final output I want to be is this:

As you can see, Four was not overridden by Null, but One and Two in Column UserOne were updated by the values in the field UserTwo.

The reason I thought this was interesting as a problem was because of the question I was asked by a junior developer asking how he would track the update step to make sure that he updated the fields to NULL and I realized that I had never really thought about it because SQL Server handles this situation quite elegantly.  You don't HAVE to track what was updated, when doing the update it will use the values that are available at time of update, it does not do in order updates at all.

In reality the only statement you have to use is provided as a single update in the example below.  Order of the columns being set doesn't matter either.

IF object_id('tempdb..#tmp_testexample') IS NOT NULL

BEGIN

   DROP TABLE #tmp_testexample

END

create table #tmp_testexample

(

       ID int identity(1,1)

       ,UserOne varchar(255)

       ,UserTwo varchar(255)

)

insert into #tmp_testexample

(

       UserOne,UserTwo

)

SELECT

'One','Two'

UNION

Select 'Two','Three'

UNION SELECT 'Four',null

SELECT * FROM #tmp_testexample

select

*

from #tmp_testexample

where UserTwo is not null

       and UserOne <> UserTwo

update tta

set tta.UserOne = tta.UserTwo

, tta.UserTwo = NULL

from #tmp_testexample tta

where UserTwo is not null

       and UserOne <> UserTwo

select

*

from #tmp_testexample

This produces the output:

Original Data Set:

ID	UserOne	UserTwo
1	Four	NULL
2	One	Two
3	Two	Three

Example of what I want to update:

ID	UserOne	UserTwo
2	One	Two
3	Two	Three

Final Result after single update statement:

ID	UserOne	UserTwo
1	Four	NULL
2	Two	NULL
3	Three	NULL