DTS SQL query to text file

[Shal2]

Hi all,

I have created a package using export wizard, which exports data to a text file by executing an SQL query. It works fine when I execute it. Now in the query I added an input parameter, this also works fine when I put a default value to the input parameter(global variables).

Now I want the user to be able to speciy this parameter for which I am using an asp page to send that value. But it is not working. How can I achieve this, is it not possible because the DTS was created by wizard. How do I modify this package to be able to accept input parameter.

My package(created by wizard) when seen in the designer has just two steps connection on(database server) and connection 2(textfile destination) and they are conencted by a line which has the sql query which is like:

SELECT * from table1

WHERE ID= ?
and the global input parameter ID is of string type.(In the database table it is an integer)

The error that I get is "Invalid character value for cast specification error"

I have tried changing the parameter type to int and etc, but not working.
Appreciate any ideas,

Thank you,
Shal.

 

[ClaireHCM]

One good way to do is put the query in a stored procedure which allows you to pass dynamic id in DTS package