Running a DTS Package from a Stored Procedure

Running a DTS Package from a Stored Procedure

Post by Stace » Sat, 13 Nov 1999 04:00:00



Hello,
I have just stared to use SQL Server 7 and I need to be able to run a
DTS package from within a stored procedure.  Can anybody tell if this
is possible and if so how this can be achieved.

Thanks in advance.

Regards

Stacey

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

 
 
 

Running a DTS Package from a Stored Procedure

Post by Neil Pik » Sat, 13 Nov 1999 04:00:00


Staecey,

Quote:> I have just stared to use SQL Server 7 and I need to be able to run a
> DTS package from within a stored procedure.  Can anybody tell if this
> is possible and if so how this can be achieved.

Q.     How can I run a DTS package from within SQL Server - e.g. a
stored-procedure?
(v1.0 1999.06.18)

A. A choice :-

1.  Run from xp_cmdshell "dtsrun dtsfile"

2.  You could probably get at the DTS package via DMO.  No example of this.

3.  Use sp_OA sp's.  Example of this is below (courtesy of Bill Hodghead)

if exists (select * from sysobjects where id =
object_id(N'[dbo].[sp_displayoaerrorinfo]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_displayoaerrorinfo]
GO

if exists (select * from sysobjects where id =
object_id(N'[dbo].[sp_displaypkgerrors]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_displaypkgerrors]
GO

if exists (select * from sysobjects where id =
object_id(N'[dbo].[sp_executepackage]') and OBJECTPROPERTY(id, N'IsProcedure')
= 1)
drop procedure [dbo].[sp_executepackage]
GO

create proc sp_displayoaerrorinfo

as




   PRINT 'OLE Automation Error Information'



   BEGIN




   END

   ELSE
   BEGIN
       PRINT '  sp_OAGetErrorInfo failed.'
       RETURN
   END
GO

create proc sp_displaypkgerrors

as









   BEGIN
       print 'Unable to get steps'

       RETURN
   END



   BEGIN
       print 'Unable to get number of steps'

       RETURN
   END


   Begin      


       BEGIN
           print 'Unable to get step'

           RETURN
       END



       BEGIN
           print 'Unable to get ExecutionResult'

           RETURN
       END



   end



   else
       print 'Packge Succeeded'

GO

create proc sp_executepackage




as


   --create a package object


   Begin

       RETURN
   end

   --load the package (ADD integrated security support)









           -- ServerName As String, [ServerUserName As String], [ServerPassword
As String], [Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default],
[PackagePassword As String], [PackageGuid As String], [PackageVersionGuid As
String], [PackageName As String], [pVarPersistStgOfHost])
       else

   else
   begin


   end


   BEGIN
       print 'LoadFromSQLServer failed'

       RETURN
   END

   --execute it


   BEGIN
       print 'Execute failed'

       RETURN
   END

   --return the step errors as a recordset

   -- unitialize the package


   BEGIN
       print 'UnInitialize failed'

       RETURN
   END

   -- release the package object


   Begin

       RETURN
   end
GO

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (356 entries) see

 or www.ntfaq.com/sql.html (+ ntfaq download)
 or http://www.swynk.com/faq/sql/sqlserverfaq.asp
 or http://www.sql-server.co.uk