Seems like an (almost) duplicate post from this one:

http://www.sqlservercentral.com/Forums/Topic1604321-364-1.aspx

There is a lot of text in your message, but actually not a single question. 😉

Personally I'd try with a simple lookup...

This is possible, but only when using partial caching (which might not be as performant). Is there a reason to use the variable?

What about just joining the staging table and...

I consider leaving out length specifications for strings (and leaving it up to the...

I have an article on how to implement SCD in SSIS using different methods and I compare them against each other.

Four Methods for Implementing a Slowly Changing Dimension in Integration...

Since you are mixing integer data types with string data types, the + operator is not used for string concatenation but for the mathematical addition.

And you can't "add" a string...

Nice question, thanks.

+2

Thanks for the question.

Not an easy one, thanks Tom.

Luis Cazares (8/14/2014)

---

Grant Fritchey (8/14/2014)

---

Evil Kraig F (8/14/2014)

---

djj (8/14/2014)

---

Wish I could remember what last year was like, I remember some nice days for '77 as I have...

GilaMonster (8/15/2014)

---

However OP said

So, no row number, no temp tables. Really limits...

IIF is shorthand for a CASE statement. I'm sure you can figure...

For such questions, it would be nice if you provided table DDL, sample data and desired output.

See the first link in my signature on how to do this.

CREATE TABLE [dbo].[Salaries](

[ID]...

GilaMonster (8/14/2014)

---

Koen Verbeeck (8/14/2014)

---

- add a column with only the value 1 in it

- create a running total on this column.

You can use the quirky update method laid out by...

I was not serious 😀

This is serious though:

- add a column with only the...

It was an attempt to a joke. Don't use CLR unless you like being miserable.

Go with the temp table/identity...