Converting negative integer from string to int

I'm having an issue using the data conversion task and trying to convert from a negative integer that is bounded by brackets e.g, (1) for -1. I keep getting an error that the value cannot be converted although I've tried all signed integer types.

Yeah that doesn't surprise me. You'll be able to do this with a Derived Column component. *I think* the following should do it (assuming the column is called yourCol:

(DT_I4)SUBSTRING(yourCol, 2, 1)

-Jamie

|||

If there are positive numbers in that column as well, you may need to place a conditional split before the derived column; and then, as Jamie suggested, use an expression (in a derived column transform) to remove the brackets and to multiply the resulting number by -1 so you get the negative sign.

|||

A Derived Column Tx would be my preferred option, as this could be done in a single transform. There is no need for the split either. The following expression should do the trick. It avoids the need for any conditional processing, are we +ve or -ve type stuff. The only thing I have not catered for is NULLs.

(DT_I4)REPLACE(REPLACE([Column], "(", "-"), ")", "")

|||Thanks Darren, I'm going to give that a try. It's bizarre because I have a DTS in SQL 2000 and it handles this issue without any problem or conversion issues.|||

whatthedilly wrote:

Thanks Darren, I'm going to give that a try. It's bizarre because I have a DTS in SQL 2000 and it handles this issue without any problem or conversion issues.

The fact that DTS does this is not a good thing. It is a very bad thing and SSIS was a conscious effort to move away from DTS's technique of effectively guessing how it should treat values. With SSIS you have to explicitly tell it how to treat values. You are in control. This removes the possibility of the engine making the wrong guess - as DTS was wont to do.

-Jamie