While I was working on SSIS Packages for a project, and was trying to schedule it with a SQL Agent job, I came across a situation.
I was using two SSIS Packages. I configured each package to be called from the SQL Agent job as a separate step, as they were to do different tasks – one dependent on the other. The first package was called as part of the second step in the job, and the second was called in the 5th step. I noticed that the job would get past the second step, third and the fourth. But on the fifth, it kept running – indefinitely.
More strange was the fact that the SSIS Package when run from the SQL Server Business Intelligence Development Studio, was running successfully (and finished, which is of more importance). But the issue was only when the package was scheduled in a SQL Agent Job.
It didn’t end there. I almost did a research on the net and wished to try something I found. It said, restart the server! The great restart! I did that and phew! It completed when I run it. I did not stop there. I tried to run once more and as expected it never completed. I restarted the SQL Server Service – it also restarted the SQL Agent Service. Then again, when I run the job, it completed. I tried once more, it never completed!! At this point I was frustrated. After a long research, I tried putting a hosts file entry. Details below.
I was using two SSIS Packages. I configured each package to be called from the SQL Agent job as a separate step, as they were to do different tasks – one dependent on the other. The first package was called as part of the second step in the job, and the second was called in the 5th step. I noticed that the job would get past the second step, third and the fourth. But on the fifth, it kept running – indefinitely.
More strange was the fact that the SSIS Package when run from the SQL Server Business Intelligence Development Studio, was running successfully (and finished, which is of more importance). But the issue was only when the package was scheduled in a SQL Agent Job.
It didn’t end there. I almost did a research on the net and wished to try something I found. It said, restart the server! The great restart! I did that and phew! It completed when I run it. I did not stop there. I tried to run once more and as expected it never completed. I restarted the SQL Server Service – it also restarted the SQL Agent Service. Then again, when I run the job, it completed. I tried once more, it never completed!! At this point I was frustrated. After a long research, I tried putting a hosts file entry. Details below.
The SSIS Package – in turn the Server was trying to reach the website http://crl.microsoft.com to call the CryptoAPI function to verify the certificates that are assigned to the SQL Server assembly files. The function CryptoAPI would check Certificate Revocation List on that site. This surely requires that the Server in on the internet. As it usually happens, the servers do not have open connection to the internet, and hence it was never completing, probably waiting to get connected.
As a solution, I put this website to point to 127.0.0.1 (self) in the hosts file – and guess what, the job started to complete successfully thereafter. Here is a screenshot of the hosts file on the server –
As a solution, I put this website to point to 127.0.0.1 (self) in the hosts file – and guess what, the job started to complete successfully thereafter. Here is a screenshot of the hosts file on the server –
It is important to note that the server I was working on is a 32-bit server.
I sincerely hope this post saves hours of time for someone!
~ Manoj Deshpande.
No comments:
Post a Comment