Re: Current System as Global Variable

From: Akber (Akber_at_discussions.microsoft.com)
Date: 08/27/04 

Date: Fri, 27 Aug 2004 04:21:02 -0700

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:

> 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@discussions.microsoft.com> wrote in message
> news:8CAB2AAB-9384-4A43-BB99-851B69541CE7@microsoft.com...
> > Thanks Darren,
> >
> > Darren I have used the following query
> >
> > insert into date_test
> > (select d_date from bis_dwh.d_date where d_date >= ? )
> >
> > in the SQLExec task and when I click on the parameters tab it gives me the
> > following message
> > "The SQL statement does not contain any parameters"
> >
> > I have created the Global variable in the ActiveX Script task just as
> Jacco
> > told and have verified that the variable has been created in the package
> > properties.
> > Why I am getting the above msg?
> >
> > "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@discussions.microsoft.com> wrote in message
> > > news:EA4D543C-7C87-4F9B-9339-A24624CB76E6@microsoft.com...
> > > > What do u mean by parameters tab.
> > > > Can u plz give an example
> > > >
> > > > "Darren Green" 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@discussions.microsoft.com> wrote in message
> > > > > news:B14366DF-9AF6-4350-A6E3-EC2F3267835C@microsoft.com...
> > > > > > Ok fine. I have created the Global variable in the VBScript task.
> But
> > > how
> > > > > to
> > > > > > refer to that variable in the SQL Queries and whether it would
> work in
> > > > > Oracle
> > > > > > or not b/c SQL Execute task would send the query to run on the
> Oracle
> > > > > > plateform. Would it replace the variable with its variable before
> > > sending
> > > > > it.
> > > > > > Like I want to use the following query
> > > > > > select * from Table where DateCol=[DateGlobalVariable]
> > > > > >
> > > > > > "Darren Green" 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@discussions.microsoft.com> wrote in message
> > > > > > > news:D7FF0114-F4A9-4D47-8F9D-5FDBDC7C97D9@microsoft.com...
> > > > > > > > Yeh I can do this in SQL like in Oracle I can have the
> following
> > > SQL
> > > > > > > > select * from Table where DateCol=sysdate
> > > > > > > >
> > > > > > > > but the problem is that my ETL takes around 5 hrs to load the
> data
> > > > > into
> > > > > > > the
> > > > > > > > warehouse and I have to schedule the ETL at 11:00 pm. So if I
> use
> > > the
> > > > > > > sysdate
> > > > > > > > in SQL queries then in one SQLExecute Step I would have
> different
> > > date
> > > > > and
> > > > > > > in
> > > > > > > > the that might execute after midnight the sysdate would return
> a
> > > > > diferent
> > > > > > > > value. So no consistency. Thats why I want to store the
> cuurent
> > > system
> > > > > > > date
> > > > > > > > in the global variable at start of the ETL and then use that
> value
> > > for
> > > > > all
> > > > > > > > the steps.
> > > > > > > >
> > > > > > > > "Darren Green" 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@discussions.microsoft.com> wrote in message
> > > > > > > > > news:45E52F58-2F2C-4BF2-A93D-25769BB535C4@microsoft.com...
> > > > > > > > > > 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.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Quantcast