I am new to DTS, and I am having a problem with
parameters. I want to run a custom stored procedure and
export the resulting records to a flat file. So I have a
SQL Server connection and a Destination Text File
connection. They are connected by a Transform Data Task.
The Source for my Transform Data Task is an SQL query to
execute the stored procedure with two hardcoded values for
the stored procedure parameters:

EXEC MS2ADW '2002','200403'

The Transformations for my Transform Data Task are simple
one-for-one field copies.

All of this works fine, but now I want to use DTS
parameters for my stored procedure. When I change my SQL
query to:

EXEC MS2ADW '2002',?

and then go to the Transformations tab, I get a message
that "Some of your transformations refer to columns that
do not exist in your source or destination", and I'm given
some options: Remove invalid transformations, Change
source/destination, or Remove all transformations and redo
auto-mapping. Essentially, I have lost all the source
columns, and cannot enter the transformations.

Does anybody know how to get around this?

Thanks in advance.....

Kay Leaman

Hi Kay,

What I understand is that you get that message when the columns are changed
in the source. Which eventually marks the Transformation as Invalid.

Just make sure that the script of the stored procedure was not changed.


HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Ashish,

My stored procedure has not changed. The only thing I've
done is change the SQL statement that invokes it (to
change a hardcoded parameter value to a question mark).

Kay