Running a DTS Packing in a Stored Proc

Does anyone know how to run a DTS package in a stored procedure?
Also, what security rights does one have to have to run the DTS package in a
stored proc?
Thanks,
Yosh>> Does anyone know how to run a DTS package in a stored procedure?
Check out the command line utility DTSRUN in SQL Server Books Online. You
should be able to call this utility using xp_cmdshell procedure.
Anith|||Use xp_cmdshell to access the DTS run utility from a stored proc. I am
pretty sure the only security rights needed is to be able to execute the
stored procedure.
Derek Davis
ddavis76@.gmail.com
"Yosh" <yoshi@.nospam.com> wrote in message
news:uh%23lU4KuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Does anyone know how to run a DTS package in a stored procedure?
> Also, what security rights does one have to have to run the DTS package in
> a stored proc?
> Thanks,
> Yosh
>|||no need for peculiarities of xp_cmdshell...this works:
EXEC sp_run_DTSPackage 0, 'DTSName', @.sql_error_code out
IF (@.sql_error_code <> 0)
begin
select @.text = 'Error -60: Execution of DTS Package DTSName failed - ERROR '
+ CONVERT(VARCHAR, @.sql_error_code)
select @.result = -60
GOTO ERROR_POINT
end
where sp_run_DTSPackage is as follows:
CREATE PROCEDURE sp_run_DTSPackage (
@.o_run_id smallint,
@.o_pkg_desc varchar(50),
@.error_code int output )
AS
-- Script: sp_run_DTSPackage.sql
-- Date: October 2004
-- Author: Marc McGuckian
--
-- Description: Use OLE Automatiopn to load and execute the DTS package
@.o_pkg_desc
--
--
-- Return Values: 0 - SUCCESS
-- 1 - DATABASE ERROR
-- 11 - Failed to create an instance of the DTS.Package OLE object
-- 12 - Failed to load package
-- 13 - Failed to set/get DTS Package variable
-- 14 - Failed to obtain system parameter value
-- 15 - Execution of DTS Package Failed
-- 16 - Failed to destroy instance of the DTS.Package OLE object
-- 20 - Package Step Failed
--
--
-- Routines Called: sp_OACreate
-- sp_OAMethod
-- sp_OASetProperty
-- sp_OAGetProperty
-- sp_OADestroy
-- sp_OAGetErrorInfo
--
-- Tables Used:
--
-- OBJECTS Created:
-- TABLES:
-- STORED PROCS:
-- TEMP TABLES:
--
-- GRANTS EXECUTE TO PUBLIC
--
-- Modification History:
--
========================================
====================================
====
-- Name Date Description
--
----
--
--
--
========================================
====================================
====
BEGIN
DECLARE @.hres int, -- HRESULT returned by OLE
method call
@.pkg int, -- Package Object
@.errsrc varchar(255), -- DTS Package Error Source
@.errdesc varchar(255), -- DTS Package Error
Description
@.prop int, -- Package Property
@.o_pkg_prop_desc varchar(150), -- Package Property Name
@.o_pkg_prop_value varchar(150), -- Package Property Value
@.o_pkg_prop_order tinyint,
@.prop_to_set char(1),
@.dtsaccess varchar(50),
@.o_syslog_desc varchar(150) -- Message to be logged to
system log
SELECT @.error_code = 0, @.prop_to_set = 'Y',
@.o_pkg_prop_order = 0, @.dtsaccess = ''
UPDATE ot_lu_dts_properties
SET o_pkg_prop_value = o_param_value
FROM ot_lu_dts_properties dts,
ot_lu_system_parameter sys
WHERE dts.o_param_code = sys.o_param_code
SELECT @.error_code = @.@.ERROR
IF (@.error_code <> 0)
BEGIN
SELECT @.o_syslog_desc = 'Failed to update DTS properties
[ot_lu_dts_properties] - ERROR : ' + CONVERT(VARCHAR, @.error_code)
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
RETURN @.error_code
END
EXEC sp_run_DTS @.dtsaccess out
--Creates an instance of the DTS.Package OLE object
EXEC @.hres = sp_OACreate 'DTS.Package', @.pkg out
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to create an instance of the
DTS.Package OLE object'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 11
RETURN 11
END
--Load DTSLoadStaging package
EXEC @.hres = sp_OAMethod @.pkg, 'LoadFromSQLServer', null, '(local)',
@.PackageName = @.o_pkg_desc, @.PackagePassword = @.dtsaccess, @.Flags = 256
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to load package'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 12
RETURN 12
END
-- Set all DTS package properties
WHILE (@.prop_to_set = 'Y')
BEGIN
-- Find the next property to set
SELECT @.prop_to_set = 'N' -- Assume no more properties to set
SELECT @.prop_to_set = 'Y',
@.o_pkg_prop_desc = pro.o_pkg_prop_desc,
@.o_pkg_prop_value = pro.o_pkg_prop_value,
@.o_pkg_prop_order = pkp.o_pkg_prop_order
FROM ot_lu_dts_package pkg,
ot_lu_dts_properties pro,
ot_lu_dts_package_properties pkp
WHERE pkg.o_pkg_desc = @.o_pkg_desc
AND pkg.o_pkg_id = pkp.o_pkg_id
AND pkp.o_pkg_prop_id = pro.o_pkg_prop_id
AND pkp.o_pkg_prop_order = ( SELECT MIN(tbl.o_pkg_prop_order)
FROM ot_lu_dts_package_properties
tbl
WHERE tbl.o_pkg_id =
pkg.o_pkg_id
AND tbl.o_pkg_prop_order >
@.o_pkg_prop_order )
IF (@.prop_to_set = 'Y')
BEGIN
-- Set DTS Package property value
EXEC @.hres = sp_OASetProperty @.pkg, @.o_pkg_prop_desc,
@.o_pkg_prop_value
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' +
convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' +
@.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE',
'ERROR', @.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to set DTS Package
property ' + @.o_pkg_prop_desc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE',
'ERROR', @.o_syslog_desc
SELECT @.error_code = 13
RETURN 13
END
END -- IF (@.prop_to_set = 'Y')
END -- WHILE (@.prop_to_set = 'Y')
--Set DTS Package Global Variables gvRun
SELECT @.o_pkg_prop_desc = 'GlobalVariables("gvRun").value'
EXEC @.hres = sp_OASetProperty @.pkg, @.o_pkg_prop_desc, @.o_run_id
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to set DTS Package Global Variable
gvRun'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 13
RETURN 13
END
--Execute Package
EXEC @.hres = sp_OAMethod @.pkg, 'Execute'
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Execution of DTS Package DTSLoadStaging
Failed'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 15
RETURN 15
END
SELECT @.o_pkg_prop_desc = 'GlobalVariables("gvPkgStatus").value'
EXEC @.hres = sp_OAGetProperty @.pkg, @.o_pkg_prop_desc, @.error_code OUT
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to obtain DTS Package Global
Variable gvPkgStatus'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 13
RETURN 13
END
SELECT @.o_pkg_prop_desc = 'GlobalVariables("gvPkgStatusDesc").value'
EXEC @.hres = sp_OAGetProperty @.pkg, @.o_pkg_prop_desc, @.o_syslog_desc OUT
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to obtain DTS Package Global
Variable gvPkgStatus'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 13
RETURN 13
END
--Clean up
EXEC @.hres = sp_OADestroy @.pkg
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to destroy instance of the
DTS.Package OLE object'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 16
RETURN 16
END
RETURN @.error_code
END
you're welcome