derived column help

Hi Guys,

How can i put in a derived column the value of 3 columns? I've tried these:

[MyId]+[Paper1]

"[MyId]+[Paper1]"

but no luck. How can i put it right?

Thanks

Gemma

The first one looks correct. [Column1] + [Column2] + [Column3]

Note: Are the three columns you are trying to combine all of the same type (i.e. unicode string, string, etc)? If not you will probably need to either convert them before trying this or cast them inside of the derived column transform.

|||

Maybe you have a type mismatch or a typo?

Try type casting and then concatenating. e.g. (DT_WSTR) [MyID] + [Paper1]

The top right box in the transformation editor contains these functions, so dont sweat the syntax, and the Double Inverted commas etc.

The top left box contains available variables and columns. Dragging and dropping your columns into the expression will avoid typos.

HTH

|||Any error messages?|||

Hi,

I have 3 different data type columns. One is int, 2nd one is varchar and 3rd one is datetime.

What can i do as still in the expression its coming up as red and i can't press ok.

Maybe i'm not putting in the function right.

Can someone tell me how to put them in the expression box?

Thanks

Gemma

|||

Hi Guys,

Here is the error message:

TITLE: Microsoft Visual Studio

Error at GetMyData [Derived Column [548]]: The data types "DT_I4" and "DT_WSTR" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Error at GetMyData [Derived Column [548]]: Attempt to set the result type of binary operation "MyID + MyNo" failed with error code 0xC0047080.

Error at GetMyData [Derived Column [548]]: Computing the expression "[MyID] + [MyNo] + [Paper1]" failed with error code 0xC0047084. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.

Error at GetMyData [Derived Column [548]]: The expression "[MyID] + [MyNo] + [Paper1]" on "output column "MyNewID" (684)" is not valid.

Error at GetMyData [Derived Column [548]]: Failed to set property "Expression" on "output column "MyNewID" (684)".

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

Gemma

|||

You must convert to string before concactenate.

Regards!!

|||

Hi,

If i knew how i would have done it by now

Can you tell me how should I with the syntax?

Thanks

Gemma

|||(DT_WSTR) [MyID] + (DT_WSTR)[Paper1]|||

David Frommer wrote:

(DT_WSTR) [MyID] + (DT_WSTR)[Paper1]

The above examples by David and karfast are close, but not quite right.

(DT_WSTR,length)[MyID] + (DT_WSTR,length)[Paper1] - (where length is the number of bytes long the string is to be.)

So, for example, you could have:
(DT_WSTR,50)[MyID] + (DT_WSTR,50)[Paper1]|||

OK, try:

(DT_WSTR,2)([Column1])+(DT_WSTR,2)([Column2])+(DT_WSTR,2)([Column3])

Replace 2 for the lenght of your columns...

I hope helped!!!

|||

Thanks pedro.

you're a life saver.

Gemma