SSIS string conversion 2012

[activejunkie]

Hi,
I have a requirement where from a string we need to derive the amount and also based on the alphabet we need to make it negative or positive amount. Here is the string
'1047383658000012345678990000002014083J354310020140850000005335R0000'
In this string '0000005335R' is the amount with 2 decimals 000000533.5R
We need to decode A = 1, B= 2 till I = 9 and J = 1 with negative values till S = 9(negative)
so the final output in this string should be:
if the string is '1047383658000012345678990000002014083J354310020140850000005335A0000'
output should be 533.51
if the string is '1047383658000012345678990000002014083J354310020140850000005335S0000'
output should be -533.59

I need expert help on this asap.

Thanks.

 

[fredericofonseca]

looks like Mainframe cobol signed (and S - 9 looks wrong by the way as R = 9)
following link has the possible values, respective replacement number and multiplier.

http://answers.microsoft.com/en-us/...i-string/5dfe5a46-a3ad-42e0-bf52-0add0cda6312

Easy enough for you to pass the string through a c# convert script within your dataflow, split the string in your components and apply the logic to the right most character.
something like
nibble = right(mystr,1)
num = left(mystr,len(mystr) - 1)
sign = "+"
case
when nibble = A
then num2 = 1
...
when nibble = J
then num2 = 1
sign = "-"
...

final_num = sign + num + num2

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[activejunkie]

Hi Frederico,
Thank you very much for your posting. Your advise helped me in resolving the issue. There is one more thread on how to resolve it, posting the link if anyone interested.

http://www.ssistalk.com/2007/03/14/ssis-working-with-cobol-zoned-signed-decimals/