DTS global variables question

[HaveTrouble]

I try to create a DTS package that will export data from several tables into a coma delimiter file. What I did is create a connection to database, then a destination file object, then create a transform data task between the 2 objects. The transform data task will execute a stored procedure to select data to be exported. The stored procedure is going to take a parameter from user. I am going to call this DTS from either ASP or VB. How can I set the parameter in such a way that it can be set from ASP or VB ? I've created a global variables object that can set the connection string from ASP/VB. I can set the whole stored procedure call using similar method but I would really like to keep the stored procedure name in DTS. Any idea ?

 

[ProdAir]

Create an Execute SQL task and put:

EXEC 'stored proc' ?

and then click the parameters button. Hope this helps.

 

[HaveTrouble]

I tried that. The stored procedure is called from "transform data task" that execute stored procedure to select matching records and export to a text file. When I use ? and parameter button, the transform is not working. It couldn't find the source fields and thus cannot do the transform. Any further tips ?

 

[maswien]

You should be able to execute a DTS package with global variables as parameters in VB or ASP. Take a look of BOL.

 

[HaveTrouble]

That's my last resort. I was trying to keep the stored procedure name hard-coded in DTS so all application calling this DTS won't use the wrong stored procedure.

However, I have tons of problem call DTS from ASP or VB. I develop the DTS and VB from my PC and it worked fine. However, when I move both up to server, i kept having "Invalid class string" error when it called "DTS.LoadSQLFromServer" function. My DTS only has a SQL source connection, text file destination, and a transform data task. If the transform data task is removed, then the error will go away. Any idea ?