Global Variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Global Variable

Author

Topic

ducepair
Starting Member

7 Posts

Posted - 2009-08-27 : 10:18:26

Hi All, i am trying to do following scenario to fetch the latest data when i execute the DTS package

Source is DB2 and target is Oracle

First i defined execute SQL task and fetching the max(load_date) from target table and store it in a variable called date1.
select max(load_date) into date1 from target_table.
Now in my Transform Data task properties i am using that variable on Source Query like this..
select * from source_table where lod_date > date1

But when i execute the DTS package, it ran successfully without any errors. Then i figured it, the variable i defined is local to that execute SQL task.

Now, i defined a Global variable and i am trying to assign the same values that come from the following query.

select max(load_date) into date1 from target_table.

like this

dim max_date
set DTSGlobalVariables("date1").Value = max_date
set max_date = 'select max(load_date) into date1 from target_table'
Main = DTSTaskExecResults_Success
EndFunction.

But my problem is i am getting systax error while set the max_date value through the SQL.

Hope everyone understad my situtation here..

It would be great if someone can help to resolve this issue?

Thanks in Advance

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 10:32:30

why dont you put this inside a sql task and then map the global variable as a output parameter

ducepair
Starting Member

7 Posts

Posted - 2009-08-27 : 23:04:49

Vishak,thanks for the reaply. I tried doing that like below
declare
@start_date
begin
set @start_date= select max(load_date) from target_table
End

but it gave me syntax error.

@ is expecting something

Is there any other way i can do this?

Thanks in advance

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-28 : 00:13:19

[code]
declare @start_date datetime
~~begin~~
select @start_date = max(load_date) from target_table
~~End~~
[/code]

ducepair
Starting Member

7 Posts

Posted - 2009-08-28 : 09:10:38

Russell, thanks for the reply. I added your script below and i parsed it. But when i run the DTS package, it failed and gave an exception

Encounterd Symbol @ expecting one of the following := ;

Since i am querying max date from Target(Oracle),it will not allow @ symbol.

Here is how my DTS package is.

Execute SQL task (to select max_date)
ODBC Data source for DB2(Source)
Transform Data Task(link between source to Target)
ODBC data source for Oracle(Target)

In transform data task i wrote a simple query

select * from source_table where date > @start_time

I am trying to select max date from target table and assign the value to @Start_time variable, so that it can fetch only latest records from source data base.

Hope you understand this.

I really apprecite if someone can help me, doing this.

Thanks a lot..

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-28 : 10:58:26

use an execute sql task to perform the SELECT and assign the output to a global variable.

in transform data task, use a question mark (?) for the variable(not @ sign), and assign it to the global variable

ducepair
Starting Member

7 Posts

Posted - 2009-08-28 : 13:20:51

Great..thanks a lot Russell. It worked for me. What i did was, i wrote a select statement in my execute SQL task and assign that to output variable. And i called that output variable in data transform Task.

Thanks a lot Russell

Thanks to visakh too...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources