Parameter in DTS

Question

Post reply

Parameter in DTS

ustreddog

Newbie

Points: 2
More actions
September 24, 2004 at 2:47 am

#164928

I have implemented a DTS that copy some data from a table to another. However, the DTS select different date and table to copy to. Therefore, I have implemented a program that will change an INI file accordingly and the DTS has been set global variables and use dynamic properties task to change the global variable from the INI file. Howver, it was found that the parameter can only be used in the where cause but not in the select/ delete cause. For example,
1. SELECT * FROM myTable WHERE myDate BETWEEN ? AND ?
2. SELECT * FROM ? WHERE myDate BETWEEN ? AND ?
3. DELETE FROM myTable WHERE myDate BETWEEN ? AND ?
4. DELETE FROM ? WHERE myDate BETWEEN ? AND ?
for 1 and 3, i parse the query correctly. However, it fails for 2 and 4. It is a must that I need to change the table name accordingly. How can I do!!??

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47797 More actions · Answer 1

Use an ActiveScript task to re-write the SQL statement.

--------------------
Colt 45 - the original point and click interface

philcart SSC-Forever Points: 47797 More actions · Answer 2

Use an ActiveScript task to re-write the SQL statement.

--------------------
Colt 45 - the original point and click interface

ustreddog Newbie Points: 2 More actions · Answer 3

but the SQL statment is in the DTS Designer. How can I change it?

Michael Kettlewell Newbie Points: 4 More actions · Answer 4

Using your statement 2 as an example:

Replace the SELECT with an EXECUTE, thus

EXECUTE sp_SelectMyTable ( @table=?, @date_from=?, @date_to=?)

Create the sp_SelectMyTable stored procedure:

CREATE PROCEDURE sp_SelectTable

@table varchar(50)

, @date_from datetime

, @date_to datetime

AS

EXECUTE ('SELECT * FROM '+@table+' WHERE myDate BETWEEN '+convert (varchar(30), @date_from)+' AND '+convert(varchar(30), @date_to ) )

GO

(You might have to pay some attention to the date conversions to suit your database.)

Consider also, adapting the stored procedure to run all of your required statements using a parameter to choose which.

MAK.

Michael Kettlewell Newbie Points: 4 More actions · Answer 5

"Pay attention Pike!"

Lets try a better looking EXEC shall we...

EXECUTE ( 'SELECT * FROM '+@table+' WHERE ErrorOccurred BETWEEN '''+convert (varchar(30), @date_from, 103 )+''' AND '''+convert(varchar(30), @date_to, 103 ) + '''' )

MAK.