Due to some operational constraints, I've written a
procedure to create, set some properties and execute a
small (time, size) DTS package from a stored procedure. It
just moves some data from one place to another with some
fairly simple tranforms. I'm using the sp_OA* family of
procedures to do this. For simplicity, I was planning on
creating the object in process. Does anyone have thoughts
on this or had problems with this?

It would be running on 2k, sp3 standard. In general, it
wouldn't have to run more than a few times a day, except 4
times a year when it might run 200 or 300 hundred times in
an 8 hour period.

Do you have to use dts for this?

There were problems with memory leaks in the sp_oa.. SPs
but I haven't had problems with them recently.

For anything like this try testing it and see what happens.
Leave it for a couple of days looping.

Due to silly constraints related to the source of the data
(inflexibility of that part of the application,
configuration flexibility (multiple scaled db's and no
linked stuff or open*), etc.), DTS is the best option for
moving the data from one server to the other. I'd have
prefered to run the DTS from a jsql ob or middle-tier part
of the application on a non-event basis, but am currently
constrained from doing that.

To protect the sql server, when creating the dts object
I'd like to run it outside the sql server process (an
option on sp_create), but I haven't got that to work yet.
Other than one MS article that I did not find helpful, I
haven't seen any examples that got that option on
sp_create to work correctly.

All basic tests are find, the looping suggestions is a
good one particularly if I weight the server down at the
same time. I'll try that to make myself feel better, if I
can't get the out of process thing to work.