SSIS package does not end when run in a SQL job
I have a very simple batch file, which simply contains "echo Hello World". The batch file is located on a NAS share. The SSIS package contains a Execute Process Task to run this batch file. The SSIS package will complete successfully when run from BIDS.
When I try to execute the SSIS package in a SQL job, the job never ends. I added a new step to the SQL job, which just runs the batch file on the NAS share, and it completes successfully. The second step is to execute the SSIS package, which is also located
in a NAS share, and it never finishes.
I turned on logging for the SSIS package, and the last thing that it wrote to the log (also located on a NAS share) before I killed the job was:
OnProgress,servername,domain\proxyaccount,Execute Process Task,{5E088CA4-6A74-48F9-8934-2498B9DD6100},{F1DE664A-6A69-458F-8E4F-86D539C88408},1/31/2012 5:19:26 PM,1/31/2012 5:19:26 PM,0,0x,Operation Complete
It's like the task is finishing, but the SQL job doesn't know that it is finished. The SQL Server version is SQL Server 2008 R2 Developer Edition with SP1 and CU3 (10.50.2789) and the server operating system is Windows 2008 R2 Enterprise SP1.
This job will run on a different SQL Server with a similar build. What could possibly be causing this problem? Is there some group policy that could be preventing SSIS packages from running executables? Other SQL jobs with SSIS packages seem to run successfully
when executing other types of tasks. Am I missing something in my SSIS package?
January 31st, 2012 9:00pm
Does the SQL Server Agent account have permissions to the NAS share?MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...
Free Windows Admin Tool Kit Click here and download it now
February 1st, 2012 7:39am
I have tried it in two NAS share locations. In one location a job proxy was given permission to the share and the job proxy was used in the RunAs for the SQL job step. The job failed to complete.
In the other location the SQL Agent account was given permission to the share and SQL Agent account was used for the SQL job step. The job failed to complete.
In both cases, the SSIS package does write to the log file in both shares. So I think that the permissions on the share is not the problem.
On the server where the SQL job is running, I can see in Task Manager two processes start: DTExec.exe and conhost.exe. Then they just stay there and do nothing.
February 2nd, 2012 2:10pm
I have a very simple batch file, which simply contains "echo Hello World". The batch file is located on a NAS share. The SSIS package contains a Execute Process Task to run this batch file. The SSIS package will complete successfully when run from BIDS.
When I try to execute the SSIS package in a SQL job, the job never ends. I added a new step to the SQL job, which just runs the batch file on the NAS share, and it completes successfully. The second step is to execute the SSIS package, which is also located
in a NAS share, and it never finishes.
I turned on logging for the SSIS package, and the last thing that it wrote to the log (also located on a NAS share) before I killed the job was:
OnProgress,servername,domain\proxyaccount,Execute Process Task,{5E088CA4-6A74-48F9-8934-2498B9DD6100},{F1DE664A-6A69-458F-8E4F-86D539C88408},1/31/2012 5:19:26 PM,1/31/2012 5:19:26 PM,0,0x,Operation Complete
It's like the task is finishing, but the SQL job doesn't know that it is finished. The SQL Server version is SQL Server 2008 R2 Developer Edition with SP1 and CU3 (10.50.2789) and the server operating system is Windows 2008 R2 Enterprise SP1.
This job will run on a different SQL Server with a similar build. What could possibly be causing this problem? Is there some group policy that could be preventing SSIS packages from running executables? Other SQL jobs with SSIS packages seem to run successfully
when executing other types of tasks. Am I missing something in my SSIS package?
1st why popping up an Echo or any other visual messages on an unattended machine?
2nd this is a very primitive logging, you need to add a more robust one.
I am curious if you use mapped drives? If you do, use UNC instead.
It is not finishing because the package throwing an error [visual] that needs to be tacked but you do not see it.
Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
February 2nd, 2012 2:14pm
I don't see any pop up messages on the server.
The logging is what was provided in the SSIS package.
The batch file is using UNC, not mapped drives.
The SSIS package runs without any errors on the server itself in BIDS.
February 2nd, 2012 2:42pm
Does anyone know if SQL Agent and thus SQL jobs are affected by Internet Explorer? The System Administrator of the servers found that on the server where the SQL job completes successfully, IE 8 is installed. On the server where the SQL job never completes,
IE 9 is installed. This is not to say that was causing the problem or not. It was just one difference that he noted.
Anyway, we added the proxy account to the local Administrators group on the server, where the job would not complete. The SQL job finished successfully. Then we took the proxy account out of the local Administrators group on the server, and the
SQL job also completed successfully. It's like the account had accept something once and then it was OK to run processes. Other SQL jobs using this proxy account are now running successfully on this problem server.
I'd like to know what the root cause of this problem was, especially if I have to do something for every single proxy account which is defined on the SQL Server instance.
Free Windows Admin Tool Kit Click here and download it now
February 2nd, 2012 5:07pm
I would be very, very surprised if IE has an effect on SQL Server Agent jobs.MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...
February 3rd, 2012 1:44am
Hi DBA_Donna,
Please refer to Jason's reply in the following link:
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/910fd7b6-56b2-4804-b4f7-2d3711d88680
And here is an article talk about troubleshooting SSIS package hanged for running:
http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2cb0bec7-0fe2-4b8d-b59e-9534388c72e7
Please feel free to ask if you have any question.
Thanks,
Eileen
TechNet
Subscriber Support
If you are TechNet
Subscriptionuser and have any
feedback on our support quality, please send your feedback here.
Free Windows Admin Tool Kit Click here and download it now
February 9th, 2012 9:02am