Hi,

I'm using DTS to upload a text file to a sql server database.

I'm trying to perform a subtraction calculation between two of the source
fields and put that in a destination field.

I've done this successfully before but in this case the source fields have
currency ('$') signs and I need to get rid of them to get scripting to
recognise them as floats.

I though something like this should work:

//************************************************** ********************
// Java Transformation Script
//************************************************** **********************

// Copy each source column to the destination column
function Main()
{
//use substr method to remove leading '$'
DTSDestination("Amount") = DTSSource("Col004").substr(1) -
DTSSource("Col003").substr(1);
return(DTSTransformStat_OK);
}

//************************************************** ************************

However it does not. Some of the source columns contain nulls but that
hasn't been a problem before. I've even tried converting the source columns
to string objects (DTSSource functions return type object) using
object.toString() but that doesn't help. Always fails on execution with no
results. When I try debugging it just says that the method is not supported.

Any help would be appreciated.

Thanks.

Ben.

Well I do not use Java but here is a VBScript version that you could
convert. This was done in an ActiveScript task not in the ActiveX transform
of a DataPump task

Function Main()

dim a,b,c

a = "1"
'$9 is the value we must parse
'9 is the value we want
b = MID("$9",2,len("$9")-1)
c = b-a

msgbox c

Main = DTSTaskExecResult_Success
End Function




--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Ben" <anonymous (AT) example (DOT) com> wrote

Thanks for your help Alan.

I ended up giving it a go using VBScript and I didn't even need to strip the
'$' - it worked it out.

This was what I came up with:

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************
' Copy each source column to the destination column
Function Main()
dim Credit, Debit
If IsNull( DTSSource("Col003") ) Then Debit = 0 Else Debit =
DTSSource("Col003")
If IsNull( DTSSource("Col004") ) Then Credit = 0 Else Credit =
DTSSource("Col004")
DTSDestination("Amount") = Credit - Debit
Main = DTSTransformStat_OK
End Function

I'm still going to see if I can get it to work in JScript because it's more
like the languages I'm used to using.

Cheers.

Ben.



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote