Tracking DTS Step errors in TSQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Tracking DTS Step errors in TSQL

Author

Topic

nreinholdt
Starting Member

8 Posts

Posted - 2004-10-15 : 12:35:15

Hello all:

I'm trying to export to Excel97 with SQLServer 2000 using DTS and having problems getting a stored procedure to return GetExecutionErrorInfo parameters for each step. The package works great.

The DTS package steps are:
- set global vars (at the moment, nothing)
- drop the Excel sheet (needed in case the sheet already exists)
- create the Excel sheet
- populate the Excel sheet

I want to catch any errors dropping the Excel sheet so that I can ignore them: others will need to be reported. My VB.NET code works great: runs the package, then for each step a call to GetExecutionErrorInfo returns my info. But I need to deploy in TSQL, where my GetExecutionErrorInfo call never returns anything. What am I doing wrong?

TSQL code (I know it's long - sorry):

Set @DTSResults = 'Export Excel worksheet operation begins...' 
print @DTSResults
EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'EXECute', NULL

Declare @Steps int
Declare @stepi VarChar(1024)
Declare @stepDesc VarChar(1024)
Declare @i int
Declare @StepErrorCode bigint
Declare @StepErrorSource VarChar(1024)
Declare @StepErrorDescription VarChar(1024)
Declare @StepErrorHelpFile VarChar(1024)
Declare @StepErrorHelpContext bigint
Declare @StepErrorIDofInterfaceWithError bigint

EXEC @HRESULT = sp_OAGetProperty @DTSPackageObject, 'Steps.Count', @Steps OUTPUT

Set @i = 1
While @i <= @Steps
Begin   

        Set @stepi = 'Steps(' + Cast(@i As VarChar(3)) + ').Description'
        EXEC @HRESULT = sp_OAGetProperty @DTSPackageObject, @stepi, @StepDesc OUTPUT

        Set @stepi = 'Steps(' + Cast(@i As VarChar(3)) + ').ExecutionResult'
        EXEC @HRESULT = sp_OAGetProperty @DTSPackageObject, @stepi, @StepResult OUTPUT
        print 'Step ' + Cast(@i As VarChar(3)) + ' ' + ISNULL(@StepDesc, '')
        If @StepResult IS NOT NULL 
                print '   Result:' + Cast(@StepResult As VarChar(10))

        Set @stepi = 'Steps(' + Cast(@i As VarChar(3)) + ').GetExecutionErrorInfo'

        EXEC @HRESULT = sp_OAMethod @DTSPackageObject, @stepi, 
                @StepErrorCode OUTPUT,
                @StepErrorSource OUTPUT,
                @StepErrorDescription OUTPUT,
                @StepErrorHelpFile OUTPUT, 
                @StepErrorHelpContext OUTPUT,
                @StepErrorIDofInterfaceWithError OUTPUT

        print '   ErrorCode:   ' + IsNull(Cast(@StepErrorCode As VarChar(30)),'')
        print '   ErrorSource: ' + IsNull(@StepErrorSource,'')
        print '   ErrorDesc:   ' + IsNull(@StepErrorDescription,'')

        Select @i = @i + 1

CONTINUE
End

Running the procedure shows this in the messages window (steps are not in order of execution):

Export Excel worksheet operation begins...

Step 1 Transform Data Task: undefined
   Result:1
   ErrorCode:   
   ErrorSource: 
   ErrorDesc:   
Step 2 Create Worksheet
   Result:1
   ErrorCode:   
   ErrorSource: 
   ErrorDesc:   
Step 3 DROP worksheet
   Result:1
   ErrorCode:   
   ErrorSource: 
   ErrorDesc:   
Step 4 Dynamic Properties Task: undefined
   Result:0
   ErrorCode:   
   ErrorSource: 
   ErrorDesc:

Thanks.

nragamuffin

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2004-10-28 : 08:04:21

Please provide the Complete Code.

nreinholdt
Starting Member

8 Posts

Posted - 2004-10-28 : 12:14:23

quote:
Originally posted by shallu1_gupta

Please provide the Complete Code.

Here it is. The basics of using sp_OA extended stored procedures to control DTS packages was taken from Listing B in a Techrepublic article by Jeffrey T. Juday titled "Execute a DTS package from a SQL Server stored procedure". It can be found at http://techrepublic.com.com/5100-6313-5164094.html#Listing%20B

Thanks for looking at this.
NR

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.p_DTSExportToExcel    Script Date: 10/15/2004 9:19:55 AM ******/

ALTER PROCEDURE [dbo].p_DTSExportToExcel
        @ExportWorkbookName VarChar(1024) = 'C:\temp.xls',
        @ExportWorksheetName VarChar(32) = 'MySheet',
        @ExportSQLObjectName VarChar(127) = 'tblMySQLTable',
        @PackageName VarChar(127) = 'Test_ExportToExcelTest3',
  @DTSResults VarChar(1024) OUTPUT
As
Begin
-- Set up and run DTS package to import records
Declare @HRESULT int
Declare @GVRESULT VarChar(1024) -- used when retrieving value of DTS global variable
Declare @StepResult VarChar(1024)
Declare @DTSPackageObject int
Declare @ErrSource int
Declare @ErrDescrip VarChar(255)
Declare @ErrMsg VarChar(255)
Declare @ServerName VarChar(127)
Declare @Flags int

Select @ErrMsg = 'Error running DTS package'

-- Create a DTS Package object
Set @DTSResults = 'Creating DTS object...'
print @DTSResults
EXEC @HRESULT = sp_OACreate 'DTS.Package2', @DTSPackageObject OUTPUT
IF @HRESULT <> 0
BEGIN
        EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
        RAISERROR (@ErrMsg,11,1)
        RETURN
END
 
Set @DTSResults = 'Loading DTS package...'
print @DTSResults
EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'LoadFromSqlServer', NULL, 
  @ServerName='MYSQLSERVER', @PackageName=@PackageName, @Flags=256
IF @HRESULT <> 0
BEGIN
        PRINT '***  Load Package failed'
        EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
        Set @DTSResults = @ErrDescrip
        print @DTSResults
        RAISERROR ( @ErrMsg,11,1)
        RETURN
END

-- NOTE: code below is commented out to allow the package to execute and allow us to scan for errors afterwards.
-- Set the FailOnError property to true
-- Set @DTSResults = 'Setting FailOnError property to TRUE...'
-- print @DTSResults
-- EXEC @HRESULT = sp_OASetProperty @DTSPackageObject, 'FailOnError', -1 --Set to true
-- IF @HRESULT <> 0
-- BEGIN
--      EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
--      RAISERROR ( @ErrMsg,11,1)
--      Set @DTSResults = @ErrDescrip
--      print @DTSResults
--      RETURN
-- END
 
 
-- Call the EXECute method
Set @DTSResults = 'Export Excel worksheet operation begins...' 
print @DTSResults
EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'EXECute', NULL
IF @HRESULT <> 0
BEGIN
        EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
        Set @DTSResults = @ErrDescrip
        print @DTSResults
        RAISERROR ( @ErrMsg,11,1)
        RETURN
END
 
-- 2004/10/14 NR *NEW* for testing

-- "Translate" this VB code to TSQL
--      For i = 1 To oPackage.Steps.Count
--              If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
--                      oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
--                                      ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
--                      MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
--              End If
--      Next i

Declare @Steps int
Declare @stepi VarChar(1024)
Declare @stepDesc VarChar(1024)
Declare @i int
Declare @StepErrorCode bigint
Declare @StepErrorSource VarChar(1024)
Declare @StepErrorDescription VarChar(1024)
Declare @StepErrorHelpFile VarChar(1024)
Declare @StepErrorHelpContext bigint
Declare @StepErrorIDofInterfaceWithError bigint

EXEC @HRESULT = sp_OAGetProperty @DTSPackageObject, 'Steps.Count', @Steps OUTPUT

Set @i = 1
While @i <= @Steps
Begin   

        Set @stepi = 'Steps(' + Cast(@i As VarChar(3)) + ').Description'
        EXEC @HRESULT = sp_OAGetProperty @DTSPackageObject, @stepi, @StepDesc OUTPUT

        Set @stepi = 'Steps(' + Cast(@i As VarChar(3)) + ').ExecutionResult'
        EXEC @HRESULT = sp_OAGetProperty @DTSPackageObject, @stepi, @StepResult OUTPUT
        print 'Step ' + Cast(@i As VarChar(3)) + ' ' + ISNULL(@StepDesc, '')
        If @StepResult IS NOT NULL 
                print '   Result:' + Cast(@StepResult As VarChar(10))
        
        Set @stepi = 'Steps(' + Cast(@i As VarChar(3)) + ').GetExecutionErrorInfo'
        EXEC @HRESULT = sp_OAMethod @DTSPackageObject, @stepi, 
                @StepErrorCode OUTPUT,
                @StepErrorSource OUTPUT,
                @StepErrorDescription OUTPUT,
                @StepErrorHelpFile OUTPUT, 
                @StepErrorHelpContext OUTPUT,
                @StepErrorIDofInterfaceWithError OUTPUT
        
        print '   ErrorCode:   ' + IsNull(Cast(@StepErrorCode As VarChar(30)),'')
        print '   ErrorSource: ' + IsNull(@StepErrorSource,'')
        print '   ErrorDesc:   ' + IsNull(@StepErrorDescription,'')
        Select @i = @i + 1

CONTINUE
End

-- 2004/10/14 NR end of new section for testing

--remove the object from memory
Set @DTSResults = 'Destroying DTS object...'
print @DTSResults
EXEC @HRESULT = sp_OADestroy @DTSPackageObject
IF @HRESULT <> 0
BEGIN
        PRINT '***  Destroy Package failed'
  EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
        Set @DTSResults = @ErrDescrip
        print @DTSResults
  RAISERROR ( @ErrMsg,11,1)
  RETURN
END
Set @DTSResults = 'Text file successfully imported.'
print @DTSResults
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

nragamuffin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources