November 9, 2007 at 4:31 am
I have a DTS package that does archiving of a table to another database. When I manually run the DTS package (or any specific part of it), it executes without any problems. I scheduled it to run each night, and the job says it executes without errors, but no data is ever moved. Could I have done something wrong when I set up the schedule? Any other ideas?
Thanks:)
Michael
November 9, 2007 at 1:17 pm
Michael,
A couple of things come to mind...
Access & permissions... The SQL Agent Service account must have the appropriate access to the destination server and permissions within the destination database. Usually errors of this nature would be noted in the Job History but... If the destination is on a different server, check the service account's ability to "Access this computer from the network" in the Local Security Policies of the destination server. Be sure the Agent Service account has a valid access path/login to the destination SQL Server and has the appropriate permissions within the destination database.
Be sure that you have the latest version of the DTS package scheduled. Right-click the DTS package, select schedule and click OK. Go to the SQL Agent Job list and compare the GUIDs in your original job and the newly scheduled one. If the GUIDs differ that may be the problem.
As a rule, I delete all versions of the DTS package except for the last one before scheduling. You can delete previous versions by right-clicking the DTS package, select Versions, highlight the previous version and click Delete.
Another possibility are the Job Step Settings. Verify that the "On failure action:" on the Advanced tab of the job step is set to "Quit the job reporting failure". To check; double-click the job (or right-click & select Properties), select the Steps tab. The "On failure action" is shown in the rightmost column. If it needs to be changed double-click (or highlight & click Edit), select the Advanced tab and change the action.
Last but not least, the DTS package logging can be checked for specific errors not necessarily reported by the job history step detail. If logging is not enabled, open the package (and without selecting any object within the package) click Package, select Properties, select the Logging tab, click the "Log package execution to SQL Server" and type the name of the instance in the text box. Remember that saving the package will create a new version!
HTH,
Art
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply