birdbyte@gmail.com wrote:
> 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