Retrieving Error Messages of DTS package in Stored Procedure
-
Once scripted, from Query Analyzer, run
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
But have a look to the e.g. provided by Johnson, you can build that into a sp with the same sort of parameters as above.
The output from xp_cmdshell into a table is very clean and useable.
The reason why I did not follow the route is the fact that xp_cmdshell requires Admin Rights.
Dave
-
You mean if I use the above script, I won;t be required to use the long script that you have posted earlier. Extremely sorry, if I am bothering too much, as I told you I am newbie in the world of DTS.
Also, what Phil posted is the output of this small script that you have posted?
-
Thank you so much. I tried the above script and with some modification I was able to retrieve the error messages in my owned defined variable.
I have couple of questions:
1. Where can I see the table #ETL_STATUS? I searched everywhere in my database but could not find it?
2.What does the symbol '#' indicate in the create table statement
3. Can't we output the stream of xp_cmdshell into a user defined normal table instead of using some special #ETL_STATUS table.?
Thank you once again. You guys are a life saver.
-
*ETL_Status is a tempory table, for the duration of the procedure, to extend it beyond that use ##ETL_Status, or a permanent table.
Thank Johnson, sorry for cryptic but have to run.
Dave
-
Thank you all of you guys for your valuable help and time. I was able to get the error messages in my own defined variable by streaming the output of the dts package into the temporary table.
Thanks
-
Wow, thats an awesome script. But is there a way of storing the "Error Description" as well?? Like...Table not added because of key constraint, or Divide by Zero exception. Is it possible to get those values?
-
I suppose it all depends on what's output by the package.
I have a DTS package the has an ExecuteSQL step that updates a table prior to extracting the table to a text file. For the package "Fail Package on First Error" is not checked and for the step "Fail Package on Step Error" is not checked. When I create a condition that causes this step to fail due to a primary key constraint I get the following output from xp_cmdshell.
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error: -2147217873 (80040E2F); Provider Error: 3621 (E25) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217873 (80040E2F); Provider Error: 2627 (A43) Error string: Violation of PRIMARY KEY constraint 'PK_tblClientList_Account'. Cannot insert duplicate key in object 'tblClientList'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
So I guess it would be a simple matter of parsing out the error string.
--------------------
Colt 45 - the original point and click interface
-
I used the code from Dave Gouws's reply (which he gathered from other search results) and have an addition.
While doing the looping in the DI_DisplayPKGErrors function insert this section of code:
/*
Get the Execution Status for the step.
1 = Waiting (not a standard return as the calling procedure would wait for the results)
2 = In Progress (not a standard return as the calling procedure would wait for the results)
3 = Inactive - This is a valid state. If the package didn't call this step because of
branching rules, this is the state. Also, the Execution Result for this step
is 1 (Failure) so it is important to ensure that the SP doesn't report a
package failure on this step.
4 = Completed
*/
EXEC @intHR = sp_OAGetProperty @intStep, 'ExecutionStatus', @intStepStatus OUTPUT
if @intHR 0
BEGIN
BEGIN
IF @INbitDebugMode = 1
PRINT '*** Unable to get step ExecutionStatus'
EXEC USP_DisplayOAErrorInfo @INintOPkg , @intRC, @OUTvchrOLEAEDesc OUTPUT, @OUTvchrErrorDesc OUTPUT, @OUTintErrorNumber OUTPUT
END
RETURN 1
END
and replace the printing of the status of the step with the following:
PRINT 'Step ' + @vchrStepName + ' (' + @vchrStepDescription + ') [' +
CASE
WHEN @intStepStatus = 3 THEN 'Skipped'
WHEN @intStepStatus = 4 THEN 'Executed'
ELSE 'State Unknown ' + CAST(@intStepStatus AS CHAR(1))
END + '] ' +
CASE
WHEN @intStepResult = 0 THEN 'Succeeded'
WHEN @intStepResult = 1 THEN 'Failed'
ELSE 'UNKNOWN:' + cast( @intStepResult AS varchar(5))
END
SET @intStepCount = @intStepCount - 1
-- If the state for the step was completed, add the step result to this count which determines
-- the number of errors for the package.
IF @intStepStatus = 4
SET @intOpkgResult = @intOpkgResult + @intStepResult
The problem I ran into was that I had a 'branch' occuring in the code where there was a success and a failure route. Even thought the failure branch never occurred, SQL Server 2000 reports that step as a failure. By checking the ExecutionStatus of the Step you can determine if the step even completed. If it did, that is the only time it should be considered an error.
Thanks for the great thread.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply