What is the code to get a DTS package to run within my Stored
Procedure? I have to kick one off for a price update from an Excel
file.

TIA

__Stephen

In message <1103046259.382630.295610 (AT) c13g2000cwb (DOT) googlegroups.com>,
srussell705 <srussell (AT) lotmate (DOT) com> writes
You can use -
xp_cmdshell to call dtsrun
OLE stored procedures to load and execute via the object model
Use sp_start_job to call an existing schedule package

Some more on the OLE stuff here-

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


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

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

I created the following SP for the sole purpose of launching a DTS package:


CREATE PROCEDURE [dbo].[ExecuteDTSPackage]

@packageName varchar(100),
@serverName varchar(20),
@errorTypeOut char(3) = 'SYS' OUTPUT,
@errorCodeOut int =0 OUTPUT

AS

DECLARE @Object int
DECLARE @HResult int
DECLARE @ErrorSource varchar(255)
DECLARE @ErrorDescription varchar(255)
DECLARE @Procedure as varchar(100)

-- LOAD A VARIABLE WITH A NEEDED PROCEDURE CALL..
SELECT @Procedure = 'LoadFromSQLServer(' + @serverName + ', "", "", 256, ,
, , ' + @packageName + ')';
-- CREATE THE OBJECT.
EXEC @HResult = master..sp_OACreate 'DTS.package', @Object OUT;

IF @HResult <> 0
BEGIN
-- Trapping error if object not created
EXEC master..sp_OAGetErrorInfo @Object, @ErrorSource OUT,
@ErrorDescription OUT
SELECT hr=convert(varbinary(4),@HResult), Source=@ErrorSource,
Description=@ErrorDescription
RETURN
END

-- GET THE DTS PACKAGE REFRENCE.
EXEC @HResult = master..sp_OAMethod @Object, @Procedure, NULL;

IF @HResult <> 0
BEGIN
-- Trapping error if Execute failed
EXEC master..sp_OAGetErrorInfo @Object, @ErrorSource OUT,
@ErrorDescription OUT
SELECT hr=convert(varbinary(4),@HResult), Source=@ErrorSource,
Description=@ErrorDescription
RETURN
END

-- EXECUTE THE DTS PACKAGE.
EXEC @HResult = master..sp_OAMethod @Object, 'Execute';

IF @HResult <> 0
BEGIN
-- Trapping error if Execute failed
EXEC master..sp_OAGetErrorInfo @Object, @ErrorSource OUT,
@ErrorDescription OUT
SELECT hr=convert(varbinary(4),@HResult), Source=@ErrorSource,
Description=@ErrorDescription
RETURN
END
GO




"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Thanks for the code. That is what I was looking for. From the earlier
posts i found the dtsrun comand line, and I used that from my C# web
app.

I will look at yours more because that is how I want to do it, SERVER
based in a single tier.

Thanks.

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