469,572 Members | 1,281 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

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

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
Jul 20 '05 #1
1 10959
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

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by William Buchanan | last post: by
3 posts views Thread by Robert Song | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.