I have a SQL 2000 server that is running the following DTS package and i just
want to append a 7 to the EXT (extension) output. If I change the Select
acctno, '7' + ext then I run the preview all looks well but then when I
click on okay it comes up with the Verifying Transformations, Remove invalid
transformations, or change source/ destination or remove all transformations
and redo auto-mapping. If I leave the default radio button highlighted it is
on Remove invalid transformations and then it removes the entire "ext" field
from the report. Thank you for any help you might be able to provide.

SELECT acctno, ext
FROM [xxxxx].[dbo].[DIRECTORY]
WHERE ACCTNO NOT LIKE '%C' AND EXT NOT LIKE '%RESIGNED%' AND (EXT NOT
LIKE '%-%') AND (EXT IS NOT NULL) AND (ACCTNO NOT LIKE 'C%') AND
(ACCTNO NOT LIKE 'B%') AND (ACCTNO NOT LIKE 'A%') AND
(ACCTNO NOT LIKE 'D%') AND (ACCTNO NOT LIKE 'H%') AND (ACCTNO NOT LIKE 'M%')
AND
(ACCTNO NOT LIKE 'O%') AND (ACCTNO NOT LIKE 'P%') AND
(ACCTNO NOT LIKE 'V%') AND (ACCTNO NOT LIKE 'AC%') AND (ACCTNO NOT LIKE
'STU%')
AND (ACCTNO NOT LIKE 'CPE%') AND (ACCTNO NOT LIKE
'MD%') AND (ACCTNO NOT LIKE 'W%') AND (ACCTNO NOT LIKE 'U%') AND
(ACCTNO IS NOT NULL) AND (DEPT NOT LIKE 'xxxxxxx%')
AND (DEPT NOT LIKE 'xxxx%') AND
(DEPT NOT LIKE 'PATIENT EX%') AND (DEPT NOT LIKE
'ZZZ%') AND (EXT NOT LIKE 'BEEP%') AND (EXT NOT LIKE 'RETIRED%') AND
(EXT NOT LIKE '%xx%')
ORDER BY acctno ASC

Hi Brenda,

Upon first glance, it appears when you add the "'7' + " you're destoying the
column heading that DTS expects. What you can do is use the AS <aliasname>
clause to give that column a new name:

SELECT acctno, '7' + ext AS myNewExt
FROM ....

Have DTS reference this new column name instead. Let us know how you make
out.

Best Regards,
Tom

Thomas Bradshaw
Data Integration Services
MyWebGrocer

--
Message posted via http://www.sqlmonster.com