March 11, 2010 at 3:13 am
I use the following stored procedures to execute DTS
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc sp_displayoaerrorinfo
@object as int
as
Declare @hr int
DECLARE @output varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)
PRINT 'OLE Automation Error Information'
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC spDisplayPKGErrors
@oPkg As integer
AS
SET NOCOUNT ON
DECLARE @StepCount int
DECLARE @Steps int
DECLARE @Step int
DECLARE @StepResult int
DECLARE @oPkgResult int
DECLARE @hr int
DECLARE @StepName varchar(255)
DECLARE @StepDescription varchar(255)
IF OBJECT_ID('tempdb..#PkgResult') IS NOT NULL
DROP TABLE #PkgResult
CREATE TABLE #PkgResult
(
StepName varchar(255) NOT NULL,
StepDescription varchar(255) NOT NULL,
Result bit NOT NULL
)
SELECT @oPkgResult = 0
EXEC @hr = sp_OAGetProperty @oPkg, 'Steps', @Steps OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get steps'
EXEC sp_displayoaerrorinfo @oPkg , @hr
RETURN 1
END
EXEC @hr = sp_OAGetProperty @Steps, 'Count', @StepCount OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get number of steps'
EXEC sp_displayoaerrorinfo @Steps , @hr
RETURN 1
END
WHILE @StepCount > 0
BEGIN
EXEC @hr = sp_OAGetProperty @Steps, 'Item', @Step OUTPUT, @StepCount
IF @hr <> 0
BEGIN
PRINT '*** Unable to get step'
EXEC sp_displayoaerrorinfo @Steps , @hr
RETURN 1
END
EXEC @hr = sp_OAGetProperty @Step, 'ExecutionResult', @StepResult OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get ExecutionResult'
EXEC sp_displayoaerrorinfo @Step , @hr
RETURN 1
END
EXEC @hr = sp_OAGetProperty @Step, 'Name', @StepName OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get step Name'
EXEC sp_displayoaerrorinfo @Step , @hr
RETURN 1
END
EXEC @hr = sp_OAGetProperty @Step, 'Description', @StepDescription OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get step Description'
EXEC sp_displayoaerrorinfo @Step , @hr
RETURN 1
END
INSERT #PkgResult VALUES(@StepName, @StepDescription, @StepResult)
PRINT 'Step ' + @StepName + ' (' + @StepDescription + ') ' + CASE WHEN @StepResult = 0 THEN 'Succeeded' ELSE 'Failed' END
SELECT @StepCount = @StepCount - 1
SELECT @oPkgResult = @oPkgResult + @StepResult
END
SELECT * FROM #PkgResult
IF @oPkgResult > 0
BEGIN
PRINT 'Package had ' + CAST(@oPkgResult as varchar) + ' failed step(s)'
RETURN 9
END
ELSE
BEGIN
PRINT 'Packge Succeeded'
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC spExecutePKG
@server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@ServerUsr varchar(255) = Null,
@IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = ''-- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Intialize Data
set @oPKG = 1
-- 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
RETURN 1
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "' + @ServerUsr + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 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
RETURN 1
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
RETURN @ret
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
when call
[ATM_Branches].[dbo].[spExecutePKG]
@server ='1xx.1xx.0.xx'
, @PkgName ='xxxxxxxxx'
, @ServerPWD='fyyyyy'
, @ServerUsr ='yyy'
it give me the following errors
Step DTSStep_DTSDataPumpTask_3 (Transform Data Task: undefined) Failed
Step DTSStep_DTSExecuteSQLTask_1 (Execute SQL Task: undefined) Succeeded
Step DTSStep_DTSDataPumpTask_2 (Transform Data Task: undefined) Failed
Step DTSStep_DTSActiveScriptTask_4 (Output_To_Excel) Failed
Step DTSStep_DTSActiveScriptTask_3 (Setup_and_Import_Files) Failed
Step DTSStep_DTSDataPumpTask_1 (Import Data) Failed
Package had 5 failed step(s)
while execute the stored procedure manauly work correctly , I use SQL server 2000
Any suggestion please
March 11, 2010 at 7:20 am
I'm confused as to why you are trying to execute step by step. I think that perhaps some of the structure of the package is getting lost by executing a step as an object instead of allowing the global package to handle the execution. There are logs that can find issues in the package if it fails during execution.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply