Understanding and troubleshooting the MOM DTS package


We see a fair number of issues relating to failure of the MOM DTS package to execute.  There can be many reasons for this but when the DTS package fails the whole MOM system may have problems as a result. 


 


If the MOM DTS package is not running correctly the MOM grooming jobs will fail.  This can lead to a database that quickly grows toward the 30 GB maximum supported size.  If autogrow is disabled (the supported setting) this will lead to the MOM system shutting down as there is no room available in the database for continued operations.  This leads to cascading backlogs.  First, the MOM server queues will full up with data that needs to go to the database.  Once the MOM server queues fill we will signal agents to start storing data locally in their local queues.   If the database is set to autogrow (not supported for MOM) we can quickly see the size expand.  The server will remain operational but very likely will suffer from degraded performance.


 


Troubleshooting DTS failures is often easy but in some cases it’s helpful to understand what is actually happening behind the scenes.  Before going further I should mention that the information provided below is to assist in troubleshooting DTS issues – it is not supported to modify the DTS job or to run the DTS job directly from SQL for typical production use.


 


The MOM 2005 DTS job is executed as a scheduled task.  Have you ever wondered about this?  A scheduled task that runs an EXE isn’t really a DTS job, right?  So what really happens.  Essentially, the EXE causes a DTS job to be extracted and that extracted DTS job is what runs in SQL.  The DTS job consists of an inner job and an outer job.  The inner job calls the outer job.  The basic flow is as follows:


 


MOM.Datawarehousing.DTSPackageGenerator.exe


Extracts DTS package for execution – source and destination databases are specified on the command line. 


 


SC_Inner_DTS_Package.DTS


SC_Outer_DTS_Package.DTS 


Extracted when scheduled task runs.  These packages are responsible for executing the actual DTS run.  The outer package is started first which makes a few adjustments to the databae and then calls the inner package.  The inner package does the actual work of moving data.  When the inner package finishes control is handed back to the outer package which readjusts the database.


 


After each DTS run an event entry is added to the application event log giving status of the last run and, if a failure has occurred, the step at which the failure was noted.  The individual steps of the DTS jobs can be reviewed if the respective DTS job is loaded into SQL Enterprise Manager. 


 


Typically you won’t see the individual .DTS packages that are extracted during scheduled task execution.  If you want to obtain these files for further troubleshooting you can run MOM.Datawarehousing.DTSPackageGenerator.exe with the /savetodisk option.  This will save out the two DTS packages which can then be imported into SQL.  From here it is possible to examine the individual steps in DTS design view.  It is also possible to execute these jobs manually from SQL for troubleshooting purposes – if this manual execution isn’t done correctly, however, you may encounter failures.  It’s probably a good idea to let us help you if you need to get this detailed.


 


There is much more detail that surrounds the execution of the DTS package.  I’ll stop here for now.  If comments warrant it I will consider adding more detail in a separate entry.


 

Comments (7)

  1. Pop's blog says:

    Just found this in my RSS inbox today, it’s a really nice overview of the DTS package for MOM.

    This…

  2. MikeWalsh says:

    How does the ReportingSettings table get updated at the end of a dts running? I seem to have an issue here. I got the DTS Package to run but it doesn’t seem to be updating the onepoint.dbo.ReportingSettings.timedtslastran column with the curren time. Is there some other way this is updated?

  3. steverac says:

    The ReportingSettings table should get updateda t the conclusion of a successful DTS run – so the first thing I would check is if the DTS job is really running successfully – check the status code in the scheduled tasks.  I would also verify in the event log that the DTS job ran all steps successfully.

    Steve

  4. howserx says:

    We’re having problems with our gooming, specifically the moving of data from OnePoint to SystemCenterReporting.  We want to reinstall this specific component(the MOM.Datawarehousing.DTSPackageGenerator.exe and corresponding assemblies).  What would we select to reinstall it?  

  5. steverac says:

    The MOM.Datawarehousing.DTSPackageGenerator.exe is installed as part of the data warehouse installation.  There is no way to use setup to install just this piece manually.  Further, I’ve not seen a situation where it was required to reinstall just this component to resolve an issue.  This function of this exe is to run as a scheduled task, unpack a SQL DTS package, run that in SQL to move the data between the operational database and the warehouse and then exit.  I would be more interested to understand what specific problems you are having with your grooming as that will determine the actions to take.  If you haven’t already, this may be a good one to direct to Microsoft CSS for assistance.  Hope that helps.

    Steve

Skip to main content