Error Handling -- sql server dts

Greg Linwood
9/29/2004 10:15:07 AM

You can also run DTS packages via their COM API using sp_OACreate. This
approach is more powerful than dtsrun in a few ways, including access to
error information. Here's an example:

CREATE PROCEDURE sp_CallDTSPkg
@Packagename varchar(1000),
@file_name varchar(1000)

AS
SET NOCOUNT ON

declare @PackageServer Varchar(20)
Set @packageServer = 'servernamehere'

DECLARE @DTSPkg int
DECLARE @hr int

DECLARE @src varchar(50)

DECLARE @Descr varchar(100)
DECLARE @result int
DECLARE @errorinfo varchar(1000)

--Create a reference to the Package
EXEC @hr = sp_OACreate 'DTS.Package', @DTSPkg OUT

if @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @src OUT, @descr OUT
--there was an error
set @result = 0

END
ELSE
BEGIN
set @result = 1
--no error continue to next step
--Connect to it
EXEC @hr = sp_OAMethod @DTSPkg, 'LoadFromSQLServer',NULL,@ServerName =
@PackageServer,@Flags = 256, @PackageName = @PackageName
if @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @DTSPkg, @src OUT, @descr OUT
--there was an error
select @errorinfo = 'OLE Automation Error Information
Source: ' + @source + ' Description: ' + @description
print @errorinfo
set @result = 0
END
ELSE
BEGIN
exec @hr = sp_OASetProperty @DTSPkg, 'GlobalVariables("file_name").value',
@file_name
set @result = 1

--no error continue to next step
EXEC @hr = sp_OAMethod @DTSPkg, 'Execute'
if @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @DTSPkg, @src OUT, @descr OUT
--there was an error
select @errorinfo = 'OLE Automation Error Information
Source: ' + @source + ' Description: ' + @description
print @errorinfo
set @result = 0
END
ELSE
BEGIN
--there was no error
set @result = 1
END
END
END

HTH

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]

Peter A. Schott
10/1/2004 12:47:12 PM

Just a note on my personal experience with this method. I'd developed a proc
that would do exactly this. One day, it just completely stopped working.
Couldn't get it to work again for anything and had to (quickly) re-develop a
solution that would work. I'd recommend using the logging within DTS and
perhaps adding some of your own inside of the DTS package for various steps.

-Pete

[quoted text, click to view]

Greg Linwood
10/2/2004 5:15:46 PM

Yes, there are certainly problems that can occur with COM? The solution I
usually use in real life implementations (when given the choice) is to
indirect the call via an out of process mechanism. For example, with VB6
this meant providing an ActiveX.exe component which is called by sp_OACreate
but performs the DTS call. Doing this gives you two major advantages -
first, you can early / vtable bind to the DTS API to perform lower level
control of the object than IDispatch allows. Second, you can collect events
to report progress etc - often a compelling reason on its own. So, calling
DTS (or any COM server) directly from sp_OACreate isn't something I'd really
recommend either.

Regards,
Greg Linwood
SQL Server MVP

"Peter A. Schott" <pschott@no.spamm.hear.drivefinancial.com> wrote in
message news:0u5rl0lh3ospgd6p820qi7v649sbgljicj@4ax.com...
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : Error Handling