How do I collect log details from the Data Transformation Services? (NETIQKB48453)

How do I gather DTS Package log data when troubleshooting?
What is the process for sending DTS Package information to NetIQ Technical Support?
How do I figure out which step failed in the Analysis Center (OLAP Processing) job?
How do I determine when the step failed?

In some instances, you may need to collect and forward DTS Package information to Technical Support.  Use the following steps to collect the DTS Package log data.  Please read the NOTE below for collecting the DTS Package information for Steps 4 and 6 of the Analysis Center (OLAP Processing) job.

To determine which step failed and the corresponding time:

1. Expand to your SQL Agent Jobs by SQL Server Enterprise Manager > SQL Server Group > SQL Server > Management > SQL Server Agent > Jobs.
2. Right-click the Analysis Center (OLAP Processing) job.
3. Select View Job History.
4. Select Show Step Details.
5. Note the failed step, listed under the Step Name column and when it failed, listed under the Run at column.
6. Close the Properties window.

To collect the DTS package information for Steps 4 and 6 of the Analysis Center (OLAP Processing) job:

1. In SQL Server Enterprise Manager, expand to Data Transformation Services > Local Packages.
2. Right-click Local Packages.
3. Select Package Logs.
4. Select Analysis Center (Process Regular Cubes).
5. Scroll through the Package Logs, selecting the time when the step failed.
6. Expand DTS Package versions and log tree.
7. Select the log entry.
8. Click Open Log.
9. Select the step that has an 'X' (failed) in the Status column.
10. Click View Error.
11. Copy the entire error and forward to Technical Support.
12. Repeat Steps 9 - 11 for each step that had failed.

Formerly known as NETIQKB48453

Steps 4 and 6 of the Analysis Center (OLAP Processing) job process the OLAP dimensions and cubes create and run DTS packages in memory, but do not save the packages. Use the following SQL queries to collect the DTS Package information:

SQL query to collect DTS package information for problems in step 4:

-- FOR PROBLEMS IN STEP 4 OF THE "Analysis Center (OLAP Processing)" JOB
-- "[040] Execute Dimension Process DTS Packages"
SELECT 'Package_Step' = s.stepname, 'Description' = t.description
  FROM msdb.dbo.sysdtspackagelog p
  JOIN msdb.dbo.sysdtssteplog s
 ON p.lineagefull = s.lineagefull
  JOIN msdb.dbo.sysdtstasklog t
 ON s.stepexecutionid = t.stepexecutionid
 WHERE p.name = 'Analysis Center [Process Dimensions]'
-- adjust datetime below as needed to get relative data for problem
   AND p.starttime > '2005-07-25 00:00:00.000'
   AND stepname LIKE '%DIMENSION'
 ORDER BY s.starttime, s.stepname, t.sequenceid

SQL query to collect DTS package information for problems in step 6:

-- FOR PROBLEMS IN STEP 6 OF THE "Analysis Center (OLAP Processing)" JOB
-- "[060] Execute Regular Cube Process DTS Packages"
SELECT 'Package_Step' = s.stepname, 'Description' = t.description
  FROM msdb.dbo.sysdtspackagelog p
  JOIN msdb.dbo.sysdtssteplog s
 ON p.lineagefull = s.lineagefull
  JOIN msdb.dbo.sysdtstasklog t
 ON s.stepexecutionid = t.stepexecutionid
 WHERE p.name = 'Analysis Center [Process Regular Cubes]'
-- adjust datetime below as needed to get relative data for problem
   AND p.starttime > '2005-07-25 00:00:00.000'
   AND stepname LIKE '%CUBEPARTITION'
 ORDER BY s.starttime, s.stepname, t.sequenceid