You may want to put any logging that you can into the package so that you can see exactly where it’s getting hung up.
SQL Server can log information about package execution and the detailed step level execution in a package to a text file. You can use the text log file to troubleshoot both package level and step level errors. T
SQL Server 2000 Steps to Enable Logging to a Text File
1. Open a package in the Data Transformation Services (DTS) Designer in Enterprise Manager.
2. On Package menu, click Properties.
3. In the Properties dialog box, click the Logging tab.
4. In the Error Handling section, type a file name in the Error file text box. For example, C:\Errordts.log. You can also click the Browse button and browse to the folder in which you want to write the log file. You can also browse to an existing error log file and have the error log append to that file.
5. Select the Fail package on first error option if you want the whole package to fail if any of the steps fail for the first time.
To schedule a DTS package using the Schedule Package option
1. In the SQL Server Enterprise Manager console tree, expand Data Transformation Services, and then click either Local Packages or Meta Data Services Packages.
2. In the details pane, right-click the Data Transformation Services (DTS) package you want to schedule, and then click Schedule Package.
3. In the Edit Recurring Job Schedule dialog box, complete the required information.
Note The scheduled package will be executed by SQL Server Agent using the permissions specified during server registration. If Windows Authentication was used, then SQL Server Agent will attempt to load the package using its own security, which may not be sufficient to load the package. For more information see Handling Package Security in DTS.
You can schedule a saved Data Transformation Services (DTS) package to execute at specific times, either once or at recurring intervals. For example:
* Daily at 12:00 midnight.
* Weekly on Sunday at 6:00 A.M.
* The first or last day of the month.
A scheduled DTS package is executed by SQL Server Agent as a job. Because SQL Server Agent controls the underlying automation for scheduling, it must be running for any scheduled packages to execute. To schedule a DTS package for execution, do one of the following:
* In SQL Server Enterprise Manager, right-click a DTS package, and then click Schedule package.
This option is the easiest way to schedule packages created in the DTS Designer for execution. However, the package needs to have been saved to either the Microsoft® SQL Server™ msdb database or SQL Server 2000 Meta Data Services and needs to exist on the local server.