quote:

---

Originally posted by delpiero

Hi all,

I am quite new to DTS and now I have 2 questions on my new DTS package, which is used to retrieve the past day's records in the audit table and transfer these records to the destination database.

Suppose I have an "Transform Data Task" which selects records from a query and transfers the data to the destination (Visual Foxpro database via ODBC). The select query is similar to this: select * from "dbo"."USERDB"."AUDIT_LOG" where changedon > dateadd(day, -1, ? ) . Actually what I want to do is to make the parameter "?" a "getdate()" default value.

When I defined a global variable for the parameter, I assigned a "TODATE" variable, with type "date", and value "28-07-2005". It doesn't seem I can put the function "getdate()" into the value field.

(1) I tried to run the package by dtsrun utility so that I can pass a value for the global variable "TODATE":
dtsrun /SMYSERVER /E /n"EXPORT_AUDIT_RECORDS" /aTODATE:7='05-08-2005'

The package ran successfully without any errors. But I found that it still used the parameter value at design time, i.e. 28-07-2005, and not the one I passed in at run time, 05-08-2005. Is there anything I specified incorrectly in my command?

(2) In my dateadd(day, -1, ?) function, suppose I also want the 2nd argument of dateadd to be a variable passed in by parameter. I typed dateadd(day, ?, ?)

Then I created a global variable INCREMENT, with type integer, and value 1 (or -1). I assigned it as the first parameter in my SQL query. But when I parsed the query, it prompts an error: Argument data type datetime is invalid for argument 2 of dateadd function." Even if I just used INCREMENT as the only parameter in my query (i.e., removed the 2nd question mark), the result is the same.

Does anyone know what the problem is?

Lots of thanks,
delpiero

---