execute DTS package from Stored Procedure

execute DTS package from Stored Procedure

Post by Eric » Wed, 15 Aug 2001 00:08:30



Is it possible, and what would be the syntax of executing a DTS package from
a stored prodcedure?

Thanks,
-Eric

 
 
 

execute DTS package from Stored Procedure

Post by Brian Mora » Wed, 15 Aug 2001 00:16:44


there is no TSQL command to execute a package. The most common workaround is
to:
a) use xp_cmdshell to issue DTSRUN
b) create an on demand SQL Agent job that runs the package... then start the
job from the proc using sp_start_job. Keep in mind that you cannot start a
job that is already running.... so this process is somewhat serial....

--

Brian Moran
SQL Server MVP
SQL Server Magazine Columnist

I proudly support the PASS SQL Server user community and its upcoming user
event,  PASS 2001 North America.

For details, visit www.sqlpass.org


Quote:> Is it possible, and what would be the syntax of executing a DTS package
from
> a stored prodcedure?

> Thanks,
> -Eric


 
 
 

execute DTS package from Stored Procedure

Post by Umachandar Jayachandra » Wed, 15 Aug 2001 03:33:26


    You can use any of the following methods:

1) xp_cmdshell to execute DTSRUN.EXE
2) Job with the package call
3) sp_OA* OLE automation SPs and the DTS object model

    For some samples, you can check out "SQL Server MVP" Darren Green's web
site at http://www.swynk.com/friends/green

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

execute DTS package from Stored Procedure

Post by Dinesh.T.K » Wed, 15 Aug 2001 06:00:12


Eric,

check out this example...
uses sp_OACreate to create DTS Package Object.


AS


-- Create Package Object


BEGIN
PRINT ' ** Create Package Object Failed **'
PRINT ''

RETURN
END

-- Load the package




BEGIN
PRINT ' ** Load Package Failed **'
PRINT ''

RETURN
END

-- Execute package




BEGIN
PRINT ' ** Execute Package Failed **'

--RETURN
END
ELSE
  PRINT ' ** Execute Package Succeeded **'

-- Clean up


BEGIN
PRINT ' ** Destroy Package Failed **'

RETURN
END

GO

Dinesh.

 
 
 

1. Error in Executing DTS package with stored procedure

I have been using this same stored procedure to execute DTS packages with
one machine. (MSSQL 7 with service pack1) I have migrated everything to
another machine where the DTS package was remade and runs fine alone.but
when called by the stored procedure gives the error message below.
Everything else seems the same to me on the two machines except the name of
the server.  Does anyone have a sense of what gives?  Thanks.  David Klass


AS

/******************************
**  Name:  sp_Run_DTS
**  Desc:  Run a DTS Package
**
**  Parameters: DTS name
**
**
**  Auth:  XXXXXX
**  Date:  XX/XX/XX
******************************
**  Change History
******************************
**  Date:
**  Author:
**  Description:
******************************/



        --S is the server name
        --E is Trusted connection or you can use
        --P password and
        --U username
        --N is the name of the DTS


*****************************
sp_Run_DTS  'DTS__DrugLoader' --trying to run the command
****************************
output
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------------------------
DTSRun:  Loading...
Error:  -2147008507 (80074005); Provider Error:  0 (0)
   Error string:  Unspecified error
   Error source:  Microsoft Data Transformation Services (DTS) Package
   Help file:  sqldts.hlp
   Help context:  713

Error:  -2147467259 (80004005); Provider Error:  52 (34)
   Error string:  Client unable to establish connection
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:

   Help context:  0

Error:  -2147467259 (80004005); Provider Error:  52 (34)

   Error string:  [DBNMPNTW]ConnectionOpen (CreateFile()).

   Error source:  Microsoft OLE DB Provider for SQL Server

   Help file:

   Help context:  0

2. SQLServer 6.0 SP3, IIS 2.0, ODBC 3.5, ASP = Login failed...

3. Execute Dts Package From Stored procedure

4. Seattle, WA - Oracle DBA needed IMMEDIATELY!!

5. Execute DTS package from stored procedure

6. USA NC Chapel Hill - Lead Web Engineer

7. Executing a DTS package from within a stored procedure

8. Problem with ADO/MSACCESS and NULL params

9. executing a DTS Package trhu a Stored Procedure

10. Executing a DTS package within a stored Procedure

11. Execute a DTS package from stored procedure

12. Newbie...Can a Stored Procedure Execute DTS package?

13. Using Stored Procedure to execute DTS package from ASP page