DTS Package Schedule runs only once, Status shows executing

15 pts.
DTS packages
Hi, I have set up a DTS Package and schedule it to run every 2 minutes (to prove it will run regularly). Unfortunately the package only runs once. The process only takes a few seconds. The job status shows "Executing Job Step '1 DTS Package1'" Is there something I'm missing? Gabs

Answer Wiki

Thanks. We'll let you know when a new response is added.

If the job status says that it’s still running the step, then the step is still running.

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.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    67,435 pointsBadges:
  • Gabs
    Hi, Thanks for the advice. I've looked at all the logging and it seems to run successfully, it just doesn't stop executing so the next process is never scheduled. I generated and SQL of the scheduled job below and added some snippets of log files after that. Any help would be greaty appreciated. Thanks, Gabriel. SQL SCRIPT BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'DTS_ImportAdvisorData_TEST_2') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''DTS_ImportAdvisorData_TEST_2'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'DTS_ImportAdvisorData_TEST_2' SELECT @JobID = NULL END BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'DTS_ImportAdvisorData_TEST_2', @owner_login_name = N'sa', @description = N'Execute package: DTS_ImportAdvisorData_TEST_2', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'DTS_ImportAdvisorData_TEST_2', @command = N'DTSRun /~Z0xE4FCE6723135DC85DFA0551B1D682C03604964E5C2A659D08A38DF10D7AE56EB3D0A6C8E5F36E7514607C97C98A417172A0F0215E124BA4E4C7E1899D906790773F1CAC54DF4199B10AAF92BFF2FD5D6831DC4AF0B379214A38ED0C64F5A3076971151A35DC2A3157265DB7C46D8C6D20934E78DB7B5967A791E810ECA452B0F2FB5283B4B33EB501F1ED1AD575ED07D916C1FF078F8AB24F4335BF390AED1D36895238EF389C3A21F0867 ', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 2, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'E:\Logs\AdvisorImportLogs', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job schedules EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'DTS_ImportAdvisorData_TEST_2', @enabled = 1, @freq_type = 4, @active_start_date = 20080821, @active_start_time = 210400, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 1, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: LOG FILE SNIPPETS From the Step logs (last snippet): DTSRun OnStart: DTSStep_DTSExecuteSQLTask_9 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_9 DTSRun: Package execution complete. From the error log (last snippet): Step 'DTSStep_DTSExecuteSQLTask_9' succeeded Step Execution Started: 21/08/2008 9:04:01 PM Step Execution Completed: 21/08/2008 9:04:01 PM Total Step Execution Time: 0 seconds Progress count in Step: 0 From the SQL Agent log: 2008-08-21 21:04:00 - ? [177] Job DTS_ImportAdvisorData_TEST_2 has been requested to run by Schedule 52 (DTS_ImportAdvisorData_TEST_2) I just don't see a line that says Job Complete like I see with other processes, eg backups
    15 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: