I want to set the current system date in a global variable and then user that
variable in diiferent SQL Execute tasks. How can I do this ?
My Database is basically the Oracle 9i and I am using DTS Packages for ETL.
I am using Microsoft OLE DB provider for Oracle. How can I set the current
system date as the global variable and then use that variable in my SQL
queries


--
Thanks
Akber.

You can use an ActiveX task with the following script:

DTSGlobalVariables("SystemDate").Value = Date()

You can use the Global Variable in your queries by using a question mark in
the place where you want the variable to be used and assigning the global
variable to it on the parameters tab.

--
Jacco Schalkwijk
SQL Server MVP


"Akber" <Akber (AT) discussions (DOT) microsoft.com> wrote

Can you not do this in SQL? I don't know Oracle, but in T-SQL I would just
do something like-

SELECT * FROM Table WHERE DateCol = CURRENT_TIMSTAMP


--
Darren Green
http://www.sqldts.com


"Akber" <Akber (AT) discussions (DOT) microsoft.com> wrote

Ok that makes sense. You use a table to store the date. I have a table that
stores various information about the current run for example, including
dates and times, around the work period for the current (ET)load. First task
populates this table for the current run. Maybe it is on the wrong server
though.

The DTS way using a global variable is to use an ActiveX Script Task to set
the variable first as Jacco suggested. This is done through the designer, so
can you explain what you perceive as the problem?


--
Darren Green
http://www.sqldts.com


"Akber" <Akber (AT) discussions (DOT) microsoft.com> wrote

You do it in a task in the designer. You can't set Global Variables to
anything but literals in the designer, so you have to use a separate task to
assign a dynamic value to a Global Variable. In different situations that
can be a Dynamic Properties task, a Execute SQL task or an ActiveX script
task, and it this case an ActiveX script task seems the easiest to me. It's
only one line.

--
Jacco Schalkwijk
SQL Server MVP


"Akber" <Akber (AT) discussions (DOT) microsoft.com> wrote

From Jacco-

You can use the Global Variable in your queries by using a question mark in
the place where you want the variable to be used and assigning the global
variable to it on the parameters tab.


--
Darren Green
http://www.sqldts.com

"Akber" <Akber (AT) discussions (DOT) microsoft.com> wrote

What do u mean by parameters tab.
Can u plz give an example

"Darren Green" wrote:

Open the Exec SQL Task, there are four buttons just above the OK, Cancel
set. One of them is Paramaters. Click it.


--
Darren Green
http://www.sqldts.com

"Akber" <Akber (AT) discussions (DOT) microsoft.com> wrote

The following SQL works fine in DTS -

insert date_test
select d_date from date_test where d_date >= ?

I appreciate your are struggling, and that is fine, but please make some
effort to do basic debugging first.
If I take your SQL and change it slightly to use a table I have, and a real
value instead of a parameter, this fails in QA-

insert into date_test
(select d_date from date_test where d_date >= getdate() )

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ')'.


--
Darren Green
http://www.sqldts.com

"Akber" <Akber (AT) discussions (DOT) microsoft.com> wrote

Thanks Darren again.

Darren I did a little bit of debugging and found the following results.

I have changed the OLE DB Provider and have tested on the SQL Server
database. The OLE DB selected was Microsoft OLE DB provider for SQL Server
and have tried the same query as before in the SQL Exec tas kand it reports
the same error u discussed. I changed the query to below

insert into date_test (d_date)
select d_date from d_date where d_date >= ?

and then I clicked the parameters tab, it works fine and asked me to assign
the Global variable name. I executed the DTS and all works fine.

But when I changed the OLE DB provider to Microsoft OLE DB Provider for
Oracle (which I must b/c my source and destination tables are in Oracle
Database), and then I clicked the parameters tab (the same query as above) it
again gives the message that "The SQL Statement does not contain any
parameters".
Now why it reports me this message. Does this mean that Microsoft OLE DB
Provider for Oracle does not support such parameters??


"Darren Green" wrote: