August 1, 2003 at 1:14 pm
Hello--
I have a DTS package which has 4 tasks. The 3rd task is an import via query from a Sybase SQLAnywhere5.0 database via ODBC connection.
I have a stored procedure which executes this DTS package on the server via OLE Automation. This is allows me to keep the processing on the database server where it belongs, reduce network traffic, and not maintain ODBC drivers on each client.
This arrangement has always worked succesfully until Wednesday when I redirected the ODBC connection from my backup data source to the live data.
When executed directly, the DTS package works. However, when executed via the Proc (and OLE Automation) the package executes but the import task fails intermittantly.
The following script produces the problem:
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
PRINT 1
END
-- Using integrated security
SET @Cmd = 'LoadFromSQLServer("FPD-DB1", "", "", 256, "", , , "Staffing.getFLSADates")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
PRINT 1
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
PRINT 1
END
-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
PRINT 1
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
PRINT 1
END
PRINT @ret
Thanks for any help: this site is a life saver.
August 1, 2003 at 3:39 pm
How does the package fail? What errors are reported, if any?
August 1, 2003 at 4:17 pm
The package does not fail--it executes. Only the task that pulls data from the SQLAnywhere db fails. There are no errors reported...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply