SP to kick off a DTS??

[zooraw]

Is there a system stored procedure that you can pass the name of a DTS package to as a parameter that will execute that DTS package? If so, what SP is it?

Thanks!

 

[Bygbobbo]

declare @run_me varchar(1000)
set @run_me = 'dtsrun /S<server> /U<user> /P<password> /N<package_name>'
EXEC [<server_name>].master..xp_cmdshell @run_me


Bygs...

 

[JayKusch]

Here is another method without the use of xp_cmdshell.

CREATE PROC spExecuteDTS
@Server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = '' -- Package Password
AS

SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)

-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer(&quot;' + @Server +'&quot;, &quot;' + SUSER_SNAME() + '&quot;, &quot;' + @ServerPWD + '&quot;, 0, &quot;' + @PkgPWD + '&quot;, , , &quot;' + @PkgName + '&quot'
ELSE
SET @Cmd = 'LoadFromSQLServer(&quot;' + @Server +'&quot;, &quot;&quot;, &quot;&quot;, 256, &quot;' + @PkgPWD + '&quot;, , , &quot;' + @PkgName + '&quot'

EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL

IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG

-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

RETURN @ret

GO

Thanks

J. Kusch

 

[zooraw]

Thanks!

 

[osjohnm]

JayKusch

I tried your code but its looking for sp_displayoaerrorinfo
which it can't find. Is this your own procedure?


John

 

[tb]

JayKusch or anybody else

How (if possible) would you set Global Variables of the DTS Package within the stored proc?



&quot;All I ask is the chance to prove that money can't make me happy.&quot; - Spike Milligan

 

[SFreilinger]

Was the following ever answered?

"I tried your code but its looking for sp_displayoaerrorinfo which it can't find. Is this your own procedure?" - osjohnm

I am having the same problem.

-Shawn

 

[JohnDTampaBay]

I believe that sp_displayoaerrorinfo was replaced with sp_OAGetErrorInfo.

You can use sp_OASetProperty within Jay's procedure above before package execution to set a Global Variable value.

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[SFreilinger]

Don't have that one either. Now, I do have SQL 7.0 and am using MSDE for development...if that makes a difference.

 

[PreacherUK]

with reference to tb's comment "How (if possible) would you set Global Variables of the DTS Package within the stored proc?"

Is this possible? To get an sp to pass/set a global variable in a dts package?

As I need my package to fail if a certain criteria is met, I have my if/else statement in my sp and would like it to return/set a varable and maybe use a little bit of activex code to fail the package