question

Maxbuddy-7921 avatar image
0 Votes"
Maxbuddy-7921 asked JeffreyWilliams-3310 commented

sql server agent job- SSIS JOB

I have a job which picks up a flat file and processes the file by insert records. The job is scheduled for every half an hour. It is a SSIS PKG But suddenly the job does not pick the file and process for hours. But the job is running. When i stop the file and re-ran the job , the job
picks up the files and process immediately. Kindly help on what could be the problem?

sql-server-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndreiFomitchev avatar image
1 Vote"
AndreiFomitchev answered

If SQL Agent Job scheduled for execution every 30 min, it will start next execution only if the previous execution has ended by time of next start. Otherwise the next start will be skipped.

You should be sure that END OF FILE processed correctly and it finishes SSIS package. It loos like your SSIS job runs forever. When you restart it manually, it starts processing file as required.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered JeffreyWilliams-3310 commented

Hi @Maxbuddy-7921 ,

  1. It seems that the execution time of your package has exceeded half an hour.

  2. Please check the execution time in the view history page. We can refer to View the Job History.

  3. Then please extend the time interval in job schedules page.

Best regards,

Mona



If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Maxbuddy-7921 ,

May I know if you have anything to update?

Best regards,
Mona

0 Votes 0 ·

Hi,

If the execution time of the Job exceeds half an hour , wont it start the job for the next consecutive time and process the file?

0 Votes 0 ·

No - that time will be skipped. For example, if the job is set to every 30 minutes and scheduled to start on the hour - it starts at 8:00am and completes at 8:40am - it will then run again at 9am. SQL will not start a new job at 8:30am because the job is already running.

For your original question - it would appear that you have built some type of infinite loop into the process. The package gets stuck in that loop and never exits - until you kill the process. To be of further help, we would need to see how you setup the package - and any code you implemented as well as how the package was deployed to the server and configured in the agent job.

1 Vote 1 ·