Hello all,
I need to share the value of a variable between tasks inside of a DTS
package.
I mean, I have three systems (A.B.C.) represent in 3 tasks.
From A system (SQL SERVER) I read a date value, then I need to select some
rows from C (ORACLE) system having that date, and finally I must insert that
rows in B system (SQL SERVER DW)
The question is how to send the date value from A system to C system.
Thanks

Using SQL Server 2000

ExecuteSQL task against the SQL Server (A)
SELECT <col> FROM tbl

Assign the value to a Global Variable using the parameters button

DataPump task between Oracle (B) and SQL Server (C)

The SourceSQLStatement against Oracle should be

SELECT <Col List> FROM tbl WHERE <col> = ?

Assign the GV to the ? using the parameters button




--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Are you suere that to assign a GV to a SQLTask (ORACLE) is the same ? used
for SQLSERVER.
Because I try to do the assignment and SQL SERVER gives me a error.
Something like this,
"HResult of 0x80040e51(-2147217839) returned. Unexpected Error."

What do you think???


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> escribió en el mensaje
news:%230qP6eaXDHA.212 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

Not every driver supports Parameters like this. It isn't that it was first assigned to by SQL Server then reused by Oracle/Informix/DB2.

Easiest way then is

Assign the value to the GV in an ExecuteSQL Statement as before. Then add an AX Script task that makes up the SourceSQLStatement of the Datapump task like this

Global Variables and SQL statements in DTS
http://www.sqldts.com/default.aspx?6,102,205,7,1


--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org