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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply