2

First post here, so please be kind!

I have a bit of a problem with a set of SQL Agent jobs (SQL Server 2014, running on Windows server 2012 R2) that I am trying to setup.

Each job is a multiple step job running as follows:

  • SSIS Step
  • SQL Command (checks the load of the SSIS)
  • System Command (runs a remote batch file via PSExec)
  • SQL command (checks the status of the prior batch)

The Batch job that we are running via PSExec can take up to an hour to complete. Also, we have not choice but to run the batch file remotely.

What is happening is while the PSExec command is running, all other SSIS steps in all other jobs hang (even those not related apart from the fact that they are SSIS jobs). As soon as the PSExec step completes, the SSIS jobs start.

Has anyone experienced this before? Does anyone have a work around that we could implement?

  • What do you mean by hang or what indication do you have that the packages do hang? Are all these jobs, with SSIS steps, running at the same time? – Shawn Melton Nov 16 '15 at 5:47
  • OK, maybe hang is not the right term. While the PSExec processes are running, the job will start, but the SSIS steps will not run until the PSExec job completes. If I look at the all executions report for the SSIS steps, I am not seeing any evidence that the SSIS has started. This only appears once the PSExec step has completed. The jobs with SSIS may be running at the same time. – Ash Nov 16 '15 at 6:38
  • So if you query SSISDB.catalog.executions for status being a value of 1 (created), 2 (running), or maybe even 5 (pending)...you get no records returned? Then the job activity for these jobs with an SSIS step (step 1 according to your info) shows, they are running (msdb.dbo.sysjobactivity would show start_execute_date as NULL)? – Shawn Melton Nov 16 '15 at 7:06
  • OK, I'll have a play around with the queries later to see where it is at. I have come up with a workaround for now, which is using PowerShell to do the remote call, which seems to be OK (I am still having issues with the return codes.....). – Ash Nov 16 '15 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.