I'm transforming a 9 character field in a flat file that represents a dollar
amount. The decimal is implied inbetween the 7 and 8 characters. Also, if the
record payment status is "X", then the last digit is an alpha character that
represents that 1) the amount is negative, and 2) the value of the last digit
of the number (the cents). Examples of the alpha characters are { = 0, J = 1,
K=2... R=9. I'm using the following logic to convert the source field to the
destination:

IF RIGHT(DTSSource("Col029"), 1) = "{" THEN _
DTSDestination("Cost_Amt") = _
LEFT (DTSSource("Col029"), 7) & "." & _
SUBSTRING (DTSSource("Col029"), 8, 1) & "0"

I'm getting a VBScript runtime error: Type Mismatch: 'SUBSTRING'. The
statement parses fine. In addition, if I run the Test in the ActiveX Script
Transformation Properties, it actually works on a lot of records before
killing. Lastly, if I take the SUBSTRING Statement out, it works just fine.
HELP!

Thanks,
Rob

Can you isolate the value on which it dies?

What is the datatype?

"Robert Hamilton" <Robert Hamilton (AT) discussions (DOT) microsoft.com> wrote in
message news:Robert Hamilton (AT) discussions (DOT) microsoft.com:

The data source is a text file and the problem seems to be when the field is
populated with all zeros (00000000). When I created a temporary field in
which the source copied directly and take out the substring function out of
the transformed field in order to see the result... when the field is all
zeros in the temporary (non-transformed field), the transformed field will
contain nothing. The transformation seems to work on all fields that are not
all zeros. I hope this helps.

"Allan Mitchell" wrote:

Have you tried a Cstr() on the column?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Robert Hamilton" <Robert Hamilton (AT) discussions (DOT) microsoft.com> wrote