473,387 Members | 1,611 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

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 11097
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Dennis | last post by:
Is it possible to call a stored procedure asynchronously from an ASP page? We would like to be able to execute a proc and have the page continue to process without having to wait for the proc to...
3
by: William Buchanan | last post by:
Hi I have the following stored proc in interbase (which might contain errors - i'm doing it off the top of my head), which I would like to convert into oracle. Can you help? What I want back is...
0
by: LisaC | last post by:
I am using ISQLW in an NT cmd script to call a bunch of scripts to build a database. One of the scripts requires an input parameter. How do I pass an input parameter through the cmd script? ...
3
by: Robert Song | last post by:
Hi all I am implementing a stored procedure which needs to recursively call itself until specific condition is reached, Could anyone give some advice about that? Thanks a lot Robert Song
0
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE...
0
by: Daniel | last post by:
how do i call a stored proc that looks like this from ADO.NET? ALTER PROCEDURE new_pzGetIDList ( @foo varbinary(8) ) i have it as a System.Byte in C#
2
by: CK | last post by:
I have this CREATE PROCEDURE dbo.cmsGetTaskOrdersAndFunding2 ( @FundingDate SMALLDATETIME, @BillingContractID INT, -- null for all contracts @Filter BIT = NULL ) AS
1
by: cwendell | last post by:
Hello; I am a DBA working on a new project with a developer using .NET and C#. I have created a Package (RECORDER_API) with a very simple Procedure in it (add_row). This package is owned by...
0
by: rsadalarasu | last post by:
Hi Everybody, I m very new to Db2. I tried compiling a stored procedure in DB2 Development Center. Build failed with error message like nmake command is unavailable..(something like that). I...
0
by: barmatt80 | last post by:
I have been trying to create a web service that calls a stored procedure that will accept one value call the sql stored procedure that returns 4 variables. I haven't done this ever before, and...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.