March 7, 2005 at 9:46 am
Here's the error I get when my job fails:
... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 104 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 104 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147217873 (80040E2F) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: 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 ... Process Exit Code 1. The step failed.
This DTS has 5 execute sql tasks and one data transformatiion task. How can I tell which execute sql task is DTSStep_DTSExecuteSQLTask_2 (it doesn't seem to be the second one)? Does anyone know of a way of getting a more detailed error string other than 'statement has been terminated'?
Thanks for any help.
March 7, 2005 at 10:00 am
open up dts in designer and right click in white space and choose package properties from the resulting menu. Then click on the logging tab and check the log package to sql server option and close dialogue and save package.
When the package runs again (and fails) run this script (got from this site) and change the package name to your package name.
USE msdb
DECLARE @package_name sysname
SET @package_name = 'YourPackageName'
SELECT
dsl.stepname AS [Step Name]
, CASE dsl.stepexecstatus
WHEN 1 THEN 'DTSStepExecStat_Waiting'
WHEN 2 THEN 'DTSStepExecStat_InProgress'
WHEN 3 THEN 'DTSStepExecStat_Inactive'
WHEN 4 THEN 'DTSStepExecStat_Completed'
END AS [Step Execute Status]
, CASE dsl.stepexecresult
WHEN 0 THEN 'DTSStepExecResult_Success'
WHEN 1 THEN 'DTSStepExecResult_Failure'
END AS [Step Execute Result]
, dsl.starttime AS [Start Time]
, dsl.endtime AS [End Time]
, dsl.elapsedtime AS [Elapsed Time]
, dsl.progresscount AS [Row Count]
, dsl.errorcode AS [Error Code]
, dsl.errordescription AS [Error Description]
FROM
dbo.sysdtspackagelog dpl
INNER JOIN dbo.sysdtssteplog dsl
ON dpl.lineagefull = dsl.lineagefull
WHERE
dpl.logdate = (SELECT
MAX(dpl1.logdate)
FROM
dbo.sysdtspackagelog dpl1
WHERE
dpl1.name = dpl.name)
AND dpl.name = @package_name
ORDER BY
dpl.starttime
March 7, 2005 at 10:19 am
Thanks for the tip about logging and the script. I forgot to mention we're running SQL 7.0 which does not allow dts package logging. Any other ideas?
March 8, 2005 at 7:19 am
Can you do a "disconnected edit" in your DTS package to determine which item is failing? Open the package in Enterprise Manager, right-click on a blank portion of the package and choose "Disconnected Edit..." Using the dialog box that appears, you may be able to narrow down which object is actually erroring, as Disconnected Edit will let you see all the properties of the various objects in the package. It'll also let you change properties, including descriptions and object names, but be careful doing that as you can hose your package if you don't change things correctly.
I think that error -2147217873 is an integrity constraint violation, are you inserting/updating data? If so, might want to check the task that's erroring for a constraint violation of some sort.
March 8, 2005 at 7:50 am
Thanks dmbaker. I'd never used that disconnected edit before and it really is helpful. I was able to determine the failing step was an insert task so now I can start there. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply