locked
DataTransformationServices task failure RRS feed

  • Question

  • My FCS server runs a task that was configured during setup to transfer the reporting data into the SystemCentreReporting db for the 14 day history in the FCS management console. This task runs at 1:00 a.m daily and finishes with the following errors ( have only included the error data from event log):

    The description for Event ID 81 from source DataTransformationServices cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    If the event originated on another computer, the display information had to be saved with the event.

    The following information was included with the event:

    Error Source: Microsoft Data Transformation Services (DTS) Package
    Error Description:Execution was canceled by user.
    Error code: 80040427
    \Error Help File:sqldts80.hlp
    Error Help Context ID:10100


    Package Name: SC_Inner_DTS_Package
    Package Description: This package transfers data from ASAS-FCS1.OnePoint to ASAS-FCS1.SystemCenterReporting
    Package ID: {480BBCA1-AB38-4E9D-AF9B-2C4E9F1CFB2E}
    Package Version: {FCE41071-25BB-44E1-A7B0-D36F776B9ABB}
    Package Execution Lineage: {B3ECA99D-7251-44C2-B2D1-3CBB4C8E2BF8}
    Executed On: ASAS-FCS1
    Executed By: das
    Execution Started: 9/03/2009 1:00:22 AM
    Execution Completed: 9/03/2009 1:31:24 AM
    Total Execution Time: 1861.859 seconds

    Package Steps execution information:

    Step 'DTSStep_ExecuteSQLTask_SC_EventFact_View_2_Insert' failed

    Step Error Source: Microsoft OLE DB Provider for SQL Server
    Step Error Description:Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    Step Error code: 80004005
    Step Error Help File:
    Step Error Help Context ID:0

    Step Execution Started: 9/03/2009 1:14:24 AM
    Step Execution Completed: 9/03/2009 1:26:40 AM
    Total Step Execution Time: 735.672 seconds
    Progress count in Step: 0

    Step 'DTSStep_ExecuteSQLTask_SC_EventFact_View_3_Insert' was not executed

    Step 'DTSStep_ExecuteSQLTask_SC_EventFact_View_4_Insert' was not executed

    Step 'DTSStep_ExecuteSQLTask_SC_EventFact_View_5_Insert' was not executed

    Step 'DTSStep_ExecuteSQLTask_SC_EventFact_View_6_Insert' was not executed

    Step 'DTSStep_ExecuteSQLTask_SC_EventParameterFact_View_1_Insert' succeeded
    Step Execution Started: 9/03/2009 1:09:11 AM
    Step Execution Completed: 9/03/2009 1:31:24 AM
    Total Step Execution Time: 1333.421 seconds
    Progress count in Step: 0

    Step 'DTSStep_ExecuteSQLTask_SC_EventParameterFact_View_2_Insert' was not executed

    Step 'DTSStep_ExecuteSQLTask_SC_EventParameterFact_View_3_Insert' was not executed

    Step 'DTSStep_ExecuteSQLTask_SC_EventParameterFact_View_4_Insert' was not executed

    SECOND ERROR MESSAGE:

    Step Error Description: (1:SC_Inner_DTS_Package) SubStep 'DTSStep_ExecuteSQLTask_SC_EventFact_View_2_Insert' failed with the following error:
    Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    Execution was canceled by user.
    Step Error Code: -2147220441
    Step Error Help File:
    Step Error Help Context ID:0

    Even if I run the task command manually removing the /silent switch, the same error occurs.

    Is there anyone out there who has defeated this same issue ??

    Thanks in advance.

    Monday, March 9, 2009 1:26 AM

All replies

  • Can you check the size on your SystemCenterReporting Db and make sure it isn't full?  Most of the DTS job failures we usually see revolve around not properly sizing that db.
    CSS Security Support Engineer (FCS/MBSA/WUA/Incident Response) Check out my blog http://blogs.technet.com/kfalde
    Monday, March 16, 2009 2:41 PM
  • Thanks for your reply Kurt, I ran a disk usage report on my systemcentrereporting db. It says as follows:
    Total Space Usage -  11859.63 MB
    Data File Usage - 5284.25 MB. 1.35% unused, 6.51% unallocated
    Transaction log space usage - 6575.38 MB. 99.6% unused, 0.4% used.

    I have set autogrowth for both REPDATA and REPLOG to 20%. I did see on another post it is better to manually set the sizes of these to 2 and 1/2 times the data expected to replicate to avoid time outs. Do you recommend this ?

    Yesterday I ran the DTS task with the /latency switch, gradually decreasing the latency value. This seemed to do the trick as the task would complete successfully but whenever the task is run without the switch, it fails with the above message.

    Looking forward to your reply.
    Tuesday, March 17, 2009 12:19 AM
  • Autogrow is not supported on those DB's so don't use it.  You need to go size your DB's appropriately for the amount of clients that you have/plan on having.  http://blogs.technet.com/kfalde/archive/2008/08/25/fcs-database-sizing.aspx and take a look at the chart there. For a more fully fleshed out article on DB sizing see http://blogs.technet.com/fcsnerds/archive/2008/09/25/fcs-with-mom-2005-database-guidance.aspx.  Once you have the DB sized appropriately you should be able to run the job without the latency switch but it may take a long time depending on how long it has been since the job last ran correctly.
    CSS Security Support Engineer (FCS/MBSA/WUA/Incident Response) Check out my blog http://blogs.technet.com/kfalde
    Tuesday, March 17, 2009 2:24 PM
  • Hi Kurt,

    I resized the database manually. We have approx 800 computers so I made the REPDATA 20GB and the REPLOG 10GB on the SystemCenterReporting db. I ran the job with the latency switch so it could catch up, which it did. All of the 14 day history was populated, as the jobs ran successfully. I let the scheduled task run its course and it still stopped with the following error.

    Log Name:      Application
    Source:        MOM.Datawarehousing.DTSPackageGenerator.exe
    Date:          20/03/2009 1:13:39 AM
    Event ID:      1001
    Task Category: None
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      ASAS-FCS1.asas.qld.edu.au
    Description:
    Step StepInvokeInnerPackage failed.
    Step Error Source: Microsoft OLE DB Provider for SQL Server
    Step Error Description: (1:SC_Inner_DTS_Package) SubStep 'DTSStep_ExecuteSQLTask_SC_EventFact_View_1_Insert' failed with the following error:
    Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    Execution was canceled by user.
    Step Error Code: -2147220441
    Step Error Help File:
    Step Error Help Context ID:0
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="MOM.Datawarehousing.DTSPackageGenerator.exe" />
        <EventID Qualifiers="0">1001</EventID>
        <Level>2</Level>
        <Task>0</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2009-03-19T15:13:39.000Z" />
        <EventRecordID>27413</EventRecordID>
        <Channel>Application</Channel>
        <Computer>ASAS-FCS1.asas.qld.edu.au</Computer>
        <Security />
      </System>
      <EventData>
        <Data>Step StepInvokeInnerPackage failed.
    Step Error Source: Microsoft OLE DB Provider for SQL Server
    Step Error Description: (1:SC_Inner_DTS_Package) SubStep 'DTSStep_ExecuteSQLTask_SC_EventFact_View_1_Insert' failed with the following error:
    Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    Execution was canceled by user.
    Step Error Code: -2147220441
    Step Error Help File:
    Step Error Help Context ID:0</Data>
      </EventData>
    </Event>

    As a result, the latest days data is not populated in the 14 day history. Looking forward to any suggestions. Thanks,

    Tony.
    Thursday, March 19, 2009 10:35 PM
  • I am still having a problem with this job. I have now also resized the Onepoint db, the EEA_DATA file is 15GB and EEA_LOG is 10GB. Does it use the ReportserverTempDB db ? This is at its defaults of 6MB for TempDB with autogrowth by 1MB unrestricted, and 2MB for TempDB_log with autogrowth by 10% restricted to 2097152MB.

    The error is as follows:

    Step 'DTSStep_ExecuteSQLTask_SC_EventFact_View_2_Insert' was not executed

    Step 'DTSStep_ExecuteSQLTask_SC_EventParameterFact_View_1_Insert' failed

    Step Error Source: Microsoft OLE DB Provider for SQL Server
    Step Error Description:Transaction (Process ID 70) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    Step Error code: 80004005
    Step Error Help File:
    Step Error Help Context ID:0

    The underlined text being the common factor every time. It seems like a time out or clash when a task step is taking a long time and the next one is trying to execute.
    I wouldn't think there is too much data there because the 14 day history is populated until the 2 days ago. I hope there is someone out there who has been through this and defeated it, if so, I look forward to hearing from you.

    Thanks.

    Wednesday, April 1, 2009 11:42 PM
  • I'm having the exact same issue, we've only got 23 managed computers at this time as we're running FCS as a POC before deploying it enterprise wide. So I know db sizes can't honestly be the issue...anyone else have any further suggestions?

    @T-Bird: you ever get this resolved??

     

    Any suggestions appreciated.

     

    Thanks,

    Jacob

    Wednesday, June 2, 2010 2:07 PM
  • Ok, I have to retract what I said earlier... I thought that 36% free space in the SystemCenterReporting DB was plenty but after expanding the DB to 4x the orignal size (from 1gb to 4gb) the task ran just fine.

     

    @T-Bird: Expand your SystemCenterReporting DB, follow the links Kurt gave on DB sizing. I'm not sure how many clients you current have, but definetly increase you're db size and i'm guessing the problem will go away.

    Thanks Kurt!

    -JW

    Wednesday, June 2, 2010 9:15 PM
  • Hi T_Bird,

     

    This error message is probably not related to the MP import. The error message itself complains about a permissions issue.

    Make sure that the "SC DW DTS" NT User group has read/ write permission on the Reporting Directory (%ProgramFiles%\SystemCenterReporting\Reporting). If not grant it read/ writes access and try running the DTS task.


    Thank You.

    Prasath Jayabal

     


     

    • Proposed as answer by Prasath.J Monday, June 21, 2010 10:48 AM
    Friday, June 18, 2010 2:25 AM
  • Are you installing FCS on Server 2003 or Server 2008? If you are using 2008, there is a switch you need to add to the task in the schedule tasks.

    If you are using 2003, this is not need and you will need to look elsewhere for the issue but if you are using 2008 and getting this issue, read this.

    http://blogs.technet.com/b/clientsecurity/archive/2010/04/28/event-id-81-and-fcs.aspx

    Hope this helps.

    Saturday, June 26, 2010 12:52 AM