Retrieving Error Messages of DTS package in Stored Procedure
-
Hi,
I have been running DTS package from my stored procedure with the following code on SQL SERVER 7:
****************************************************************
CREATE PROCEDURE sp_error_test
AS
DECLARE @ErrorMessage VARCHAR(255)
BEGIN
DECLARE @errnum int
Execute @errnum = master..xp_cmdshell 'dtsrun /S UOGRAD /N dts_error_test /E '
SET @errnum = CONVERT(varchar, @errnum)
IF @errnum=0
PRINT 'Error Number is: ' + CONVERT(varchar, @errnum)
END
****************************************************************
Everything works fine with the above code i.e. I can retrieve the information if the DTS package has been executed successfully or not by checking the value of ‘errnum’ variable. I am not able to retrieve the error string/message that the dts package throws when we run the package in the Query Analyser.
For e.g. I get the following error message when I run the dts package(through Stored Procedure) in the query analyzer.
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Drop table [online_test].[dbo].[businessSchoolTestDatanulltest] Step
DTSRun OnFinish: Drop table [online_test].[dbo].[businessSchoolTestDatanulltest] Step
DTSRun OnStart: Create Table [online_test].[dbo].[businessSchoolTestDatanulltest] Step
DTSRun OnFinish: Create Table [online_test].[dbo].[businessSchoolTestDatanulltest] Step
DTSRun OnStart: Copy Data from businessSchoolTestData-org to [online_test].[dbo].[businessSchoolTestDatanulltest] Step
DTSRun OnError: Copy Data from businessSchoolTestData-org to [online_test].[dbo].[businessSchoolTestDatanulltest] Step, Error = -2147217887 (80040E21)
Error string: Error opening datafile: The system cannot find the file specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 2 (2); Provider Error: 2 (2)
Error string: Error opening datafile: The system cannot find the file specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: Copy Data from businessSchoolTestData-org to [online_test].[dbo].[businessSchoolTestDatanulltest] Step
DTSRun: Package execution complete.
(25 row(s) affected)
Server: Msg 50000, Level 11, State 1, Procedure sp_error_test, Line 16
0
How can I get those error messages like ‘Error string: Error opening datafile: The system cannot find the file specified’ in a variable in the stored procedure? Is there any inbuilt variable likes @@error in which I can get those error strings that are thrown in Query Analyzer?
-
Best solution is to enable DTS package logging. Then you'll be able to query the sysdtspackagelog and sysdtssteplog tables in the msdb database for specific error information.
--------------------
Colt 45 - the original point and click interface
-
Thanks!!! I am a newbie in using DTS package. Can you tell me how do I enable logging while executing DTS package. Also does anyone have any example which shows how to access sysdtspackagelog and sysdtssteplog tables.
Any help will be greatly appreciated.
-
You enable logging from the Logging tab of the Package Properties dialog. Just right-click on a blank space on the design panel and choose "Properties"
To get the logging detail take a look at my script in the Script Library,
http://www.sqlservercentral.com/scripts/contributions/556.asp
--------------------
Colt 45 - the original point and click interface -
Thanks!!! That looks awesome. I will try and let you know how it goes. Thank you so much.
-
Well, I went into the Package Properties after right-clicking in the blan space but I could not find the option for logging. I have 3 tabs when I click on package properties: General, Global Variables, Advanced. None of these have logging option. Can you tell me what exactly is written for that option. I have just one matching option which says : Write completion status to event Log.
Please help.
-
Is this because I am using SQL Server 7 and not 2000?
-
Ahh ... yes, missed that important bit of information in your original post.
In SQL 7, I think there is an option to output to a text file and I believe you only option is to parse that text file for errors.
Sorry
--------------------
Colt 45 - the original point and click interface -
Hi, I have a sp that will launch a DTS package, query the result and return errors/success.
It converts hex error value to char message, and returns all info (you can fine-tune) it to your needs.
All functionality is contained within the sp_OA###### procedures, ie DI_DisplayOAErrorInfo , sp_OAMethod , sp_OACreate.
It is a fairly lengthy procedure, and I see there is no "attach" facility, let me know, I'll script and post it.
Documentation on above procedures is detailed enough for you to be able to implement it
-
That would be really kind of you if you can post that script. Also will I have to use username and password method(User:sa) to connect to database. Right now I am using Windows Authentication method to connect to database from my ASP.NET pages.
-
Post the script in the Script Library so all can enjoy.
--------------------
Colt 45 - the original point and click interface -
Hi
Please be aware: the script is as is, and developed from a bit of Google research...where applicable thank you to the original authors.
Copy and paste:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DI_DisplayOAErrorInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DI_DisplayOAErrorInfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DI_DisplayPKGErrors]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DI_DisplayPKGErrors]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DI_ExecuteDTS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DI_ExecuteDTS]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DI_HexaDecimal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DI_HexaDecimal]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DI_DisplayOAErrorInfo
@object int,
@hresult int
AS
DECLARE @output varchar(255)
DECLARE @hrhex char(10)
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(255)
PRINT 'OLE Automation Error Information'
EXEC DI_HexaDecimal @hresult, @hrhex OUTPUT
SELECT @output = ' HRESULT: ' + @hrhex
PRINT @output
EXEC @hr = sp_OAGetErrorInfo @object, @source OUTPUT, @description OUTPUT
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 OFF
GO
SET ANSI_NULLS ON
GO
-- display errors from DI_ExecuteDTS execution
CREATE PROCEDURE DI_DisplayPKGErrors
@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 DI_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 DI_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 DI_DisplayOAErrorInfo @Steps , @hr
RETURN 1
END
EXEC @hr = sp_OAGetProperty @Step, 'ExecutionResult', @StepResult OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get ExecutionResult'
EXEC DI_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 DI_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 DI_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)'
SELECT '9 iSSUES'
RETURN 9
END
ELSE
BEGIN
PRINT 'Packge Succeeded'
SELECT 'Success'
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DI_ExecuteDTS
@server varchar(255), -- Required: SQL Server name
@PkgName varchar(255), -- Required: Package Name
---(Defaults to most recent version)
@ServerPWD varchar(255) = Null, -- Optional: Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0, -- Optional: 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = '' -- Optional: Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successful execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object
/*
The main procedure, DI_DisplayOAErrorInfo, takes the return code
from any of the standard OLE Automation stored procedures,
and displays the error Source and Description using
sp_OAGetErrorInfo. It also calls DI_HexaDecimal to convert the integer
error code into a string (char) representation of the true
hexadecimal value. */
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC DI_DisplayOAErrorInfo @oPKG, @hr
RETURN 1
-- RETURN Values: Used to determine the execution of the sproc. FYI: 1 equals an OLE Error
END
--PRINT '*** Create Package object Succeeded'
-- Evaluate Security and Build LoadFromSQLServer Statement
/* Syntax: Package.LoadFromSQLServer ServerName, [ServerUserName],[ServerPassword], [Flags],[PackagePassword], [PackageGuid],[PackageVersionGuid], [PackageName], [pVarPersistStgOfHost] */
-- ServerName = Server name.
-- ServerUserName = Server user name.
-- ServerPassword = Server user password.
-- Flags = Value from the DTSSQLServerStorageFlags constants indicating
-- user authentication type.
-- PackagePassword = Package password if the package is encrypted.
-- PackageGuid = Package identifier, which is a string representation
-- of a globally unique identifier (GUID).
-- PackageVersionGuid = Version identifier which is a string representation of a GUID.
-- PackageName = Package name.
-- pVarPersistStgOfHost = Screen layout information associated with a package (for internal use only).
-- Syntax: SUSER_SNAME()
/* This is the user security identification number. server_user_sid,
which is optional, is varbinary(85). server_user_sid can be the
security identification number of any Microsoft SQL Server login or
Microsoft Windows NT user or group. If server_user_sid is not
specified, information about the current user is returned. */
-- sp_OAMethod Calls a method of an OLE object.
-- Syntax:
-- sp_OAMethod objecttoken, methodname [ , returnvalue OUTPUT ] [ , [@parametername = ] parameter [ OUTPUT ] [ ...n ] ]
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD +
'", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "", "", 256, "' +
@PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
print '*** LoadFromSQLServer success'+@Cmd
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'+@Cmd
EXEC DI_DisplayOAErrorInfo @oPKG , @hr
RETURN 1
END
--PRINT '*** LoadFromSQLServer Succeeded'
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC DI_DisplayOAErrorInfo @oPKG , @hr
RETURN 1
END
-- Check Pkg Errors
EXEC @ret=DI_DisplayPkgErrors @oPKG
-- Un-initialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
--PRINT '*** UnInitialize failed'
EXEC DI_DisplayOAErrorInfo @oPKG , @hr
RETURN 1
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC DI_DisplayOAErrorInfo @oPKG , @hr
RETURN 1
END
PRINT @RET
RETURN @ret
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DI_HexaDecimal
@binvalue varbinary(255),
@hexvalue varchar(255) OUTPUT
AS
DECLARE @charvalue varchar(255)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = '0123456789abcdef'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint--(@firstint*16)
SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Thanks for posting the script. Looks like a complex one. I have a very silly question. Where should I give the name of my DTS package that I want to execute in your script. I have stored my dts package in the SQL Server 7 itself under the 'Local Package' tab under Data Transformation Services.
-
Here you have an alternative. I am not sure if it help you
create a variable to store the dtsrun command with paramters
SET @SQLCMD = 'DTSRUN /F "' + @PKG_PATH + '\DATAMARTPROCESS.DTS"'
+ ' /A COMPANY_CODE:8=' + @COMPANY_CODE
VARCHAR(25)) + '"'
/*create a temporart table */
CREATE TABLE #ETL_STATUS(IDNO INT IDENTITY(1, 1), STATUS VARCHAR(255));
/*insert the run details into the table created above*/
INSERT INTO #ETL_STATUS EXEC MASTER..XP_CMDSHELL @SQLCMD
SET @TOTAL_REC = @@ROWCOUNT;
SET @V_LOOP = 1;
SET @status = '';
WHILE @V_LOOP <= @TOTAL_REC
BEGIN
SELECT @status = @status + ISNULL(STATUS, '') FROM #ETL_STATUS WHERE IDNO = @V_LOOP;
SET @V_LOOP = @V_LOOP + 1;
END
/* ETL_RUN_INFORMATION is user table in the user database*/
UPDATE ETL_RUN_INFORMATION SET <RUN_DETAILS > = @status
WHERE DW_PROCESS_ID = (SELECT ISNULL(MAX(DW_PROCESS_ID), 1) FROM ETL_RUN_INFORMATION WITH(NOLOCK));
Johnson
-
Now that's a much simpler alternative. The output from xp_cmdshell is streamed into a table.
Once the messages are there you can poke and prod them to work out what you need.
Correct me if I'm wrong, the following is from SQL 2000, but the output would be along the lines of,
Step 'DTSStep_DTSDataPumpTask_1' succeeded Step Execution Started: 31/03/2004 3:39:42 PM Step Execution Completed: 31/03/2004 3:39:55 PM Total Step Execution Time: 12.703 seconds Progress count in Step: 30 Step 'DTSStep_DTSDataPumpTask_2' failed Step Error Source: Microsoft Data Transformation Services (DTS) Package Step Error Description:Execution was canceled by user. (Microsoft Data Transformation Services (DTS) Data Pump (80040e4e): General error -2147217842 (80040E4E).) Step Error code: 80040427 Step Error Help File:sqldts80.hlp Step Error Help Context ID:4700 Step Execution Started: 31/03/2004 3:39:42 PM Step Execution Completed: 31/03/2004 3:39:55 PM Total Step Execution Time: 12.921 seconds Progress count in Step: 1000
--------------------
Colt 45 - the original point and click interface
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply