I have a DTS Package using ActiveX Transform - have a
column GL_ACCOUNT is varchar 10. Goal is to tranform to
first xx-xxx-xxxx. The LEFT and the RIGHT string
functions work fine. Am getting type mismatch error when
using SUBSTRING - any ideas why? Any help would be
greatly appreciated!! Thanks!

Not without seeing

1. What does the source look like?
2. What is the destination definition?
3. What is the syntax you are using?


--

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

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


"KY_Kipper" <anonymous (AT) discussions (DOT) microsoft.com> wrote

The source field is type varchar - length of 10 - data is all number
The destination field is varchar size 4
want destination to be 99-999-9999, first 9 char of source field in this forma

If I remove the substring and just concatanate the first 3 and last 4 using left() and right() it works with no errors. I have checked the syntax for substring and can see nothing wrong

vbscript code is belo

Thanks for any insight

'************************************************* ********************
' Visual Basic Transformation Scrip
' Copy each source column to th
' destination colum
'************************************************* **********************

Function Main(
DTSDestination("AcctId") = DTSSource("GL_ACCOUNT"
DTSDestination("AcctIdMasked")=LEFT(DTSSource("GL_ ACCOUNT"),2)+"-"+SUBSTRING(DTSSource("GL_ACCOUNT"),3,3)+"-"+SUBSTRING(DTSSource("GL_ACCOUNT"),6,4
DTSDestination("Desc") = DTSSource("GL_ACCT_DESC"
Main = DTSTransformStat_O
End Functio