DTS package execution stops although task completes successfully

• Lifeboy

  Grasshopper

  Points: 19

  More actions

  January 13, 2007 at 7:52 am

  #67909

  I have a data transformation task ina package that reports "task completed succesfully".  In the Package logs and the text file I use for error logging it also reports success. (return code 0).  The next workflow step that should now be executed is a Stored Procedure (Execute SQL task), but the whole package execution simply stops (as I suspect it would had an error actually occurred in the first step above).

  

  Here's the text log:

  <log>

  The execution of the following DTS Package succeeded:

  Package Name: Import ITC

  Package Description: Import an ITC dataset into DWT

  Package ID: {84D3C57C-2686-4BF8-A334-9CC9C5F51ECF}

  Package Version: {D381D83B-BDF2-436F-B941-D3C81B9644A0}

  Package Execution Lineage: {1BF339A6-2A7B-4A76-9A92-0FF219B390F4}

  Executed On: ABENET03

  Executed By: administrator

  Execution Started: 13/01/2007 16:17:21

  Execution Completed: 13/01/2007 16:17:52

  Total Execution Time: 31 seconds

  Package Steps execution information:

  Step 'Copy Data from Client Output to [Consumer Marketing].[dbo].[dwi_data_warehouse_import] Step' succeeded

  Step Execution Started: 13/01/2007 16:17:21

  Step Execution Completed: 13/01/2007 16:17:52

  Total Step Execution Time: 30.969 seconds

  Progress count in Step: 23311

  Step 'DTSStep_DTSExecuteSQLTask_1' was not executed

  Step 'DTSStep_DTSDataPumpTask_1' was not executed

  Step 'DTSStep_DTSDataPumpTask_2' was not executed

  Step 'DTSStep_DTSExecuteSQLTask_2' was not executed

  Step 'DTSStep_DTSExecuteSQLTask_3' was not executed

  Step 'DTSStep_DTSExecuteSQLTask_4' was not executed

  Step 'DTSStep_DTSCreateProcessTask_1' was not executed

  ****************************************************************************************************

  </log>

  I'm logging error input and output rows separately (although I did turn this off to see if that was not causing the problem) and this is the output:

  <log>

  DATA TRANSFORMATION SERVICES: Data Pump Exception Log

  Package Name: Import ITC

  Package Description: Import an ITC dataset into DWT

  Package ID: {84D3C57C-2686-4BF8-A334-9CC9C5F51ECF}

  Package Version: {D381D83B-BDF2-436F-B941-D3C81B9644A0}

  Step Name: Copy Data from Client Output to [Consumer Marketing].[dbo].[dwi_data_warehouse_import] Step

  @@ExecutionStarted

  Execution Started: 13/01/2007 16:17:28

  @@LogSourceRows: C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ITC Import Errors.txt.Source

  @@LogDestRows: C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ITC Import Errors.txt.Dest

  @@ExecutionCompleted

  Execution Completed: 13/01/2007 16:17:52

  ****************************************************************************************************

  </log>

  What is causing this behaviour?  I haven't used packages with "on success" workflows before, so maybe I'm missing something. 

  System details are:

  SP4

  "Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)

  May  3 2005 23:18:38

  Copyright (c) 1988-2003 Microsoft Corporation

  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)"

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  January 15, 2007 at 4:20 am

  #683181

  This is puzzling.  The executesql step 1 is not disabled is it?

  Russel Loski, MCSE Business Intelligence, Data Platform

• Stefan K

  SSCommitted

  Points: 1786

  More actions

  January 15, 2007 at 6:00 am

  #683191

  Hi,

  I had the same error when using one Microsoft Data Link für two parallel running processes in one dts package. The connection was blocked and this caused the package to fail.

  You could check the tables in msdb for the error. In the table sysdtssteplog they get logged. Use the following statement to make it easier to find the error line:

  SELECT     TOP 100 PERCENT dbo.sysdtssteplog.stepexecutionid, dbo.sysdtspackagelog.name AS PackageName, dbo.sysdtspackagelog.logdate,

         dbo.sysdtssteplog.stepname, dbo.sysDTSTaskDescriptions.TaskDescription, dbo.sysdtssteplog.stepexecstatus, dbo.sysdtssteplog.stepexecresult,

         dbo.sysdtssteplog.starttime, dbo.sysdtssteplog.endtime, dbo.sysdtssteplog.elapsedtime, dbo.sysdtssteplog.errorcode,

         dbo.sysdtssteplog.errordescription, dbo.sysdtssteplog.progresscount, dbo.sysdtssteplogExt.RowsComplete, dbo.sysdtssteplogExt.RowsInError

  FROM         dbo.sysdtssteplog INNER JOIN

         dbo.sysdtspackagelog ON dbo.sysdtssteplog.lineagefull = dbo.sysdtspackagelog.lineagefull INNER JOIN

         dbo.sysDTSPackagesToLog ON dbo.sysdtspackagelog.name = dbo.sysDTSPackagesToLog.PackageName INNER JOIN

         dbo.sysDTSTaskDescriptions ON dbo.sysdtspackagelog.name = dbo.sysDTSTaskDescriptions.PackageName AND

         dbo.sysdtssteplog.stepname = dbo.sysDTSTaskDescriptions.StepName LEFT OUTER JOIN

         dbo.sysdtssteplogExt ON dbo.sysdtssteplog.stepexecutionid = dbo.sysdtssteplogExt.stepexecutionid

  ORDER BY dbo.sysdtspackagelog.name, dbo.sysdtspackagelog.logdate DESC, dbo.sysdtssteplog.starttime DESC

   

  Best regards,

  Stefan

  ---

  Kindest Regards,

  SK