August 18, 2003 at 3:55 am
Hi all,
I need to run a DTS Package (importing data from Access) from a stored procedure.
I'm not really familiar with MS SQL Server, so i got no idea about the syntax (or even if it's possible to run DTS packages in that way).
thx, Andi
August 18, 2003 at 6:11 am
More than one way to skin a cat..
You could use sp_OA.... stored procedures to instantiate and execute your DTS package. You could also execute dtsrun by using xp_cmdshell from within your stored procedure. Another way is to schedule your DTS package as a job and execute the job from your stored procedure.
Cheers.
Joseph
August 18, 2003 at 6:55 am
Use xp_cmdshell...it's much easier than using the sp_OA procs...
ciao ciao
ciao ciao
August 18, 2003 at 6:59 am
The Stored Procedure below is what I used to run a DTS package.
--------------------------------------------
IF OBJECT_ID('dbo.up_RUN_DTSPACKAGE') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.up_RUN_DTSPACKAGE
IF OBJECT_ID('dbo.up_RUN_DTSPACKAGE') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.up_RUN_DTSPACKAGE >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.up_RUN_DTSPACKAGE >>>'
END
go
CREATE PROC dbo.up_RUN_DTSPACKAGE
(
@sPackage varchar(255),
@ServerInstanceName varchar(255) ='(local)',
@ImportJobID int = 0,
@FileNamePath varchar(255) = NULL,
@ErrorCode int output,
@outstr varchar(255) output,
@outdescription varchar(255) output,
@UploadCtr int output
)
as
/******************************************************************************
* Proc Name: up_RUN_DTSPACKAGE
* File Name: up_RUN_DTSPACKAGE.sql
* Created: 09/27/2001, SalehW
* Purpose: Execue a DTS pacake by calling a SP.
* Modified: 02/05/2002, SalehW, Return the number of rows transferred via UploadCtr
* captured from the DTS job.
* 01/31/2002, SalehW, @ServerInstanceName added to SP to accommodate
* a different name instant.
* 10/05/2001, SalehW, CollinsK Set the Connection name to a file name
* with full path via imput paramter @FileNamePath.
* 10/02/2001,Salehw Added Global variable gvImportID
* Desc: This procedure can be call by users with the appropriate permission
* to start the execution of DTS packages.
* In the DTS package a Global variable with the name gvImportJobID.
* The value of the gvImportJobID is set to equal to
* Parameters:
* INPUT : @sPackage - Name of the DTS package to be executed
* OUTPUT: @ErrorCode - error code retuned from the OLE-Automation exteneded
* stored procedure calls
* @outstr - descreption of the error code
* @outdescription - Detailed descreption
* Note: No need to change the database name in this SP.
*
* Test:
DECLARE @Start_Time datetime, @RtnErr int
SELECT @Start_Time = GETDATE()
DECLARE @sPackage varchar(64),
@ServerInstanceName varchar(255),
@ImportJobID int,
@FileNamePath varchar(255),
@ErrorCode int,
@outstr varchar (255),
@outdescription varchar(255),
@UploadCtr int
SELECT @sPackage='zSQL2000_Import_PriceAdminQuoteUpload_FromCSV'
SELECT @ImportJobID = 802
SELECT @ServerInstanceName = CONVERT(char(20), SERVERPROPERTY('servername'))
SELECT @FileNamePath = '\\CORSCMV01\SCMData\PriceAdminQuote\f_salehw_01312002094911.csv'
EXEC up_RUN_DTSPACKAGE @sPackage, @ServerInstanceName, @ImportJobID,@FileNamePath, @ErrorCode output, @outstr output, @outdescription output, @UploadCtr
SELECT @sPackage, @ErrorCode, @outstr, @outdescription, @UploadCtr
------------------------------
SELECT round(datediff(mi,@Start_Time,GETDATE()) / 1440,0) As 'DD',
round((datediff(mi,@Start_Time,GETDATE()) % 1440) / 60, 2) AS 'HH',
round(((datediff(mi,@Start_Time,GETDATE()) % 1440) % 60), 2) AS 'MM',
datediff(ss,@Start_Time,GETDATE()) AS 'SS',
datediff(ms,@Start_Time,GETDATE()) AS 'MS'
******************************************************************************/
set nocount on
declare
@pkgID int,
@hr int,
@STR varchar(255),
@description varchar(255),
@property int
SELECT
@pkgID = 0,
@hr = 0,
@STR = '',
@description = '',
@property = 0
---------------------------------------
--Creates an instance of the DTS.Package/OLE object
exec @ErrorCode = sp_OACreate 'DTS.Package',
@pkgID output
--Obtains OLE Automation error information
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
if @ErrorCode <> 0 GOTO Exit_status
---------------------------------------
--Load the Package from LoadFromSQLServer
exec @ErrorCode = sp_OAMethod @pkgID,
'LoadFromSQLServer',
null,
@ServerName = @ServerInstanceName,
-- @ServerUserName = '',
-- @ServerPassword = '',
@Flags = 256,
@PackageName = @sPackage
--Obtains OLE Automation error information
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
if @ErrorCode <> 0 GOTO Exit_status
---------------------------------------
/*
--Get the proparty gvImportJobID
EXEC @ErrorCode = sp_OAGetProperty @pkgID, 'GlobalVariables ("gvImportJobID").Value', @property OUT
IF @ErrorCode <> 0
BEGIN
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
if @ErrorCode <> 0 GOTO Exit_status
END
select @property AS 'gvJobImportID'
*/
---------------------------------------
exec @ErrorCode=sp_OASetProperty @pkgID, 'GlobalVariables ("gvUploadCtr").Value', 0
--Obtains OLE Automation error information
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
if @ErrorCode <> 0 GOTO Exit_status
---------------------------------------
--Set the property gvImportJobID = 1000
IF @ImportJobID > 0
BEGIN
exec @ErrorCode=sp_OASetProperty @pkgID, 'GlobalVariables ("gvImportJobID").Value', @ImportJobID
--Obtains OLE Automation error information
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
if @ErrorCode <> 0 GOTO Exit_status
/* --DEBUG lines
--Get the proparty gvImportJobID
EXEC @ErrorCode = sp_OAGetProperty @pkgID, 'GlobalVariables ("gvImportJobID").Value', @property OUT
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
select @property AS 'gvJobImportID'
if @ErrorCode <> 0 GOTO Exit_status
*/
END
---------------------------------------
--Set the Connection 1 property set to the file path and name passed to the SP in @FileNamePath
IF @FileNamePath IS NOT NULL
BEGIN
exec @ErrorCode=sp_OASetProperty @pkgID, 'Connections ("Connection 1").Datasource', @FileNamePath
--Obtains OLE Automation error information
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
if @ErrorCode <> 0 GOTO Exit_status
---------------------------------------
/* --DEBUG lines
EXEC @ErrorCode = sp_OAGetProperty @pkgID, 'Connections ("Connection 1").Datasource' ,@description OUT
select @description AS 'Connection name'
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
if @ErrorCode <> 0 GOTO Exit_status
*/
END
---------------------------------------
---------------------------------------
--Execute the Package
exec @ErrorCode = sp_OAMethod @pkgID, 'Execute'
--Obtains OLE Automation error information
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
if @ErrorCode <> 0 GOTO Exit_status
---------------------------------------
--Return the number of rows transferred via the DTS package.
EXEC @ErrorCode = sp_OAGetProperty @pkgID, 'GlobalVariables ("gvUploadCtr").Value', @property OUT
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
select @UploadCtr = @property
if @ErrorCode <> 0 GOTO Exit_status
---------------------------------------
--Delete the Package
exec @ErrorCode = sp_OADestroy @pkgID
--Obtains OLE Automation error information
exec sp_OAGetErrorInfo @pkgID,
@STR OUT,
@description OUT
---------------------------------------
---------------------------------------
---------------------------------------
Exit_status:
if @ErrorCode <> 0
begin
select @outstr = 'error: ' + @STR
select @outdescription = @description
end
else
begin
select @outstr = 'Completed' --'Success'
select @outdescription = @sPackage + 'Load completed successfully'
end
go
IF OBJECT_ID('dbo.up_RUN_DTSPACKAGE') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.up_RUN_DTSPACKAGE >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.up_RUN_DTSPACKAGE >>>'
go
August 18, 2003 at 8:11 am
IF scheduling the pack is not possible I like triggering DTS from Stored Procs.
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<DECLARE @objPackage int
DECLARE @PackageName varchar(128)
DECLARE @rc int
DECLARE @ServerName varchar(128)
DECLARE @DatabaseName varchar(128)
SET @PackageName = 'DTS_Example'
SET @ServerName = @@ServerName
exec sp_OACreate 'DTS.Package', @objPackage output
exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("strFileString").value', @FileName
exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("InfoID").value', @infoID
exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("GroupID").value', @GroupID
exec @rc = sp_OAMethod @objPackage, 'Execute'
exec @rc = sp_OADestroy @objPackage
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
*Note @FileName,@infoID,@GroupID are local variable inside the Stored Proc
Hope that helps
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply