HELP!! HOW TO CALL A DTS PACKAGE FROM A STORED PROC

[Cox News Server]

I'm a rookie with MSSQL. I need to run a DTS package to export a result set
to an MX Excel spread sheet. I need to call the DTS from a stored procedure
and pass it three values, depending on the input parameters to the stored
proc.

DTS package is no problem. Pretty easy with the DTS wizard. My problem is
that I can't figure out how to instansiate the DTS package object from a
stored proc and pass the three values as parameters to the DTS package so
they can populate the parameters I created in it.

I found an article related to it, but I'm too much of a rookie to grasp it.
It showed how to do this from a stored procedure:

EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("MyGVName").Value,
'MyGVValue'
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
END

I have three values and tree global variables to populate. Do I need to do
the above 3 times?

How do I instantiate the package object? I've read up some on sp_OACreate,
but I don't get it, yet.

How do I initiate the variable @oPKG?
It contains the name of the sp_OACreate string, right? How do I address a
DTS package in the sp_OACreate string?

I would really appreciate just writing out the sp_OACreate string and how I
pass values for three existing global variables to a DTS package named
"DTS_1".

I'm under some real pressure to get this done.

Thanks for any help I can get.
Gunny

[John Bell]

Hi

Check out

http://www.databasejournal.com/featu...le.php/1459181

A good resource for everything DTS are books online and
http://www.sqldts.com

John

"Cox News Server" <cf*****@cox.net> wrote in message
news:RJClb.97602$AH4.96936@lakeread06...

I'm a rookie with MSSQL. I need to run a DTS package to export a result set to an MX Excel spread sheet. I need to call the DTS from a stored procedure and pass it three values, depending on the input parameters to the stored
proc.

DTS package is no problem. Pretty easy with the DTS wizard. My problem is
that I can't figure out how to instansiate the DTS package object from a
stored proc and pass the three values as parameters to the DTS package so
they can populate the parameters I created in it.

I found an article related to it, but I'm too much of a rookie to grasp it. It showed how to do this from a stored procedure:

EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("MyGVName").Value,
'MyGVValue'
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
END

I have three values and tree global variables to populate. Do I need to do
the above 3 times?

How do I instantiate the package object? I've read up some on sp_OACreate,
but I don't get it, yet.

How do I initiate the variable @oPKG?
It contains the name of the sp_OACreate string, right? How do I address a
DTS package in the sp_OACreate string?

I would really appreciate just writing out the sp_OACreate string and how I pass values for three existing global variables to a DTS package named
"DTS_1".

I'm under some real pressure to get this done.

Thanks for any help I can get.
Gunny