I am developing a Data Process Package where I would
process data into a table using a stored procedure then
export it using DTS. Could I execute the DTS package from
a Stored Procedure? This way I would simplify the process
for the operators. I needed to process tha data in a
stored procedure so I could have the option to enter a
date range. I know I could execute DTS using a batch file
but this does not give me an option to prompt for a date
range.

Thanks,

Noel V. Meneses
Programmer
Contra Costa County Health Plan
595 Center Ave.
Martinez CA

In article <014e01c39335$da050e20$a301280a (AT) phx (DOT) gbl>, Noel V. Meneses
<NMeneses (AT) hsd (DOT) co.contra-costa.ca.us> writes
You could execute DTS from a batch file through DTSRUN, and use the /A
parameter to pass in your variables as global variables.

Or

Execute a package from T-SQL
(http://www.sqldts.com/default.aspx?210)

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

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

I use this method as it seems cleaner to me... I found this on the web
sorry but I forgot who actually wrote it... I have working examples also if
you need.

thanks
tony o

Code follows:

Create procedure sp_DTS_Load_COSy_Stage --(@InConnection varchar (255))
as

set nocount on

declare @pkg int, @rc int

--Create an instance of a DTS package
exec @rc = sp_OACreate 'DTS.Package', @pkg output

--Load the baby up
exec @rc = sp_OAMethod @pkg
,'LoadFromSQLServer'
,null
,@Servername = "(local)"
,@Flags = 256 --NT authentication
,@PackageName = "COSy ODS - LOAD TXT TO COSy_STAGE"

--Set any properties of the package
--exec @rc = sp_OASetProperty @pkg
-- ,'GlobalVariables ("TextFileConnection").Value'
-- ,@InConnection

--do it
exec sp_OAMethod @pkg
,'Execute'

--destroy the instance
exec sp_OADestroy @pkg
Go

"Noel V. Meneses" <NMeneses (AT) hsd (DOT) co.contra-costa.ca.us> wrote

Hi Tony,

I have some questions about the code you sent me.

--Create an instance of a DTS Package
exec @rc = sp_OACreate 'DTS.Package', @pkg output

Question: sp_OACreate - is this a stored procedure? If so I have no
idea what the source code is like.

Same questions for : sp_OAMethod, sp_OASetProperty & sp_OADestroy

Thanks for your help,

Sincereley

Noel


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

sp_OACreate is a stored procedure, or to be correct it is really an extended
stored procedure. You do not need to know the source as it already exists in
the master database, as supplied by MS. Just lookup sp_OACreate in the index
of SQL Server Books Online if you want to know more about it.


--
Darren Green
http://www.sqldts.com

"Noel Meneses" <nmeneses (AT) hsd (DOT) co.contra-costa.ca.us> wrote

My problem now is having the permission to execute these extended stored
procedures. I searched in the Master database and it doesn't exist,
maybe I even don't have the right to view it. On SQL Server Books
Online it states that "Permissions: Only members of the sysadmin fixed
server role can execute sp_OACreate". Do I really need to have a
sysadmin fixed server role or is there any other way?

Thanks,

Noel

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

In article <u8p2AtMlDHA.3024 (AT) tk2msftngp13 (DOT) phx.gbl>, Noel Meneses
<nmeneses (AT) hsd (DOT) co.contra-costa.ca.us> writes
You can grant execute rights on the sp_OA* procedures to other users,
but this isn't good practice. You could create a wrapper proc and only
grant permission on that instead.

A bit more info on the topic-

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

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