I'm trying to set up a dts package that will eventually run
automatically every day. A sql query gets the data and formats it as a
string and dumps it into a text file. It also concatenates a variable
(that is not part of any table) to the beginning of the string. When I
run the query in sql it works fine. When I run it as part of a DTS
package it throws an error saying the transformations need to be
specified. When I click on the transformations tab I get an error
saying specify a valid name and ordinal values for the column. However,
there is nothing listed in the source column. In the DTS package I have
the source - a microsoft ole db provider for sql server, the
destination - a text file, and transform data task between the two.

Any ideas? Here is the sql statement:

Declare @Acct char(10)
set @Account = '4678209116'

SELECT @Account + RTRIM(void)+ ' ' +
RTRIM(COALESCE(REPLICATE('0', 10-LEN(cknum)),'') + cknum)+
RTRIM(COALESCE(REPLICATE('0', 13-LEN(CONVERT(varchar(12),dbamt))),'') +

(REPLACE(CONVERT(varchar(12),dbamt), '.', ''))) +
REPLACE(CONVERT(varchar(10), printed, 101), '/', '')
FROM cro.dbo.DBCKS
WHERE DATEDIFF(DAY,printed,GETDATE())= 0 AND printed = 1
ORDER BY cknum

I figured it out. If anyone's interested, I got rid of the @account and
just put the value in directly SELECT '5136845659' + ...
and I added AS StringName at the end of the select statement.
I also had to change the field size for account, because at first it
was trying to put everything in 10 spaces. When I changed that it
worked fine.


birdbyte (AT) gmail (DOT) com wrote: