I'm currently using a DTSDataPumpTask to copy data from
one database to another. The SourceSQLStatement is a
simple SELECT. So far so good.

Now I'd like to replace the SELECT with a stored proc
taking 1 input parameter and 1 OUTPUT parameter, like this:
GetAccount (@startDate datetime, @enddate datetime OUTPUT)

I've read "Global Variables and SQL statements in DTS"
(http://www.sqldts.com/Default.aspx?205), but it did not
help since I'm using DTSDataPumpTask and the
object.OutputGlobalVariableNames property is only
available with ExecuteSQLTask2 Object.

Any suggestions?
TIA

And what do you want to do with the result ?

Is the output param the only thing of interest here? Do you actually have a
rowset in the SP that you want to move as well?
Which version of SQL Server are you using ?

Global Variables and Stored Procedure Parameters
(http://www.sqldts.com/Default.aspx?234)

--

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


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

In article <099a01c3bdd5$4258b7a0$a001280a (AT) phx (DOT) gbl>, Nghia
<anonymous (AT) discussions (DOT) microsoft.com> writes
Why would you want an output parameter in a DataPump? A DataPump expects
a result set, and I cannot se why you want anything else.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

More details below.



The orginal DataPump uses a SELECT statement to copy a
list of accounts from one server to another. This code is
working in Production so I cannot change the result set.
Now I'm required to call a SP because:
- The SP hides the underlying table(s)/view(s)
- I need to filter the data

The SP is : GetAccount(@startDate datetime,
@endDate datetime OUTPUT)
set @endDate = getdate()
return all accounts that have LastUpdate is between
@startDate and @endDate.

The DataPump is part of a DTS package that runs regularly,
every 1 hour. Each time, the DTS package saves the
@endDate (in the destination db) and will pass it in as
the @startDate for the next run. So each time, I will
extract any accounts that have changed since the last time
I visited the source server.

So the question is how do I get back both a resultset and
an output parameter?


TIA
Nghia

In article <0b9d01c3bdf8$2777af40$a001280a (AT) phx (DOT) gbl>,
anonymous (AT) discussions (DOT) microsoft.com writes
The DataPump just does not offer this support.

You could -

create a second stored procedure that returns your output data as
another column in the result set. An ActiveX Script transform could then
be used to read this value and store in a global variable.

Personally I would create a a new proc or a wrapper proc that manages
the dates for you and keep state of the dates in another table. This way
no parameters are required. The date range is the current date and the
date stored in this new table. At the end of the proc update the date.
You would need this package to be transactional however.

These themes could be altered slightly, another common scenario is to
transfer the changed date and store it at the destination. The beginning
of the package now uses an exec SQL TAsk to get the max changed date
which becomes an input parameter (using > operator) for your DataPump
source statement.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org