SQL Agent Job doesn't execute SSIS package, but doesn't fail

2
votes

I set up a job to run a SSIS package in the SSIS Catalog, but the SSIS package doesn't start. If I right-click the package and run it, it completes successfully.

When I look in the execution history of the package in the Catalog, there is no sign that it the package was even started by the job.

I created a test package to make sure it isn't the specific package failing, so the package I'm testing with has a SQL Task that does a 'SELECT 1'. Still fails.

The job just stays in the "Executing" state until I stop the job.

I am running SQL Server 2014 Standard Edition, and running the job with a sysadmin account.

Why is the SQL Agent Job not running my package?

Edit: I had a look through this article but it either doesn't apply or I gave it a shot and it didn't work either.

Edit 2: I found an error under the SQL Server Agent Job History: Unable to terminate process 1850 launched by step 1 of job 0x5DAD5416FA09C445B82ABDBB49F75E38 (reason: Access is denied)

Does that mean my service account doesn't have enough permissions on the local machine? However, I AM able to run the job with DTEXEC using the service account without issues...

3
sql-serverssissql-agent-job
"...and running the job with a sysadmin account." If you're running it as a scheduled job (Server agent), isn't it run by the account that 'owns' that service? I think so. Anyway, how is the Package stored (file or in db)? Just noticed you mentioned "Catalog", so I guess in the db. – Mackan
I set up the account that "owns" the service as a sysadmin in the SQL Server Configuration Manager. – confusedKid
When you right-click the package and run it from the SSIS catalog successfully, are you doing that from the same account that you're running the Agent job as? – Jo Douglass
Yes, I am running Management Studio as the service account, and connected to the server with Windows Authentication. – confusedKid
If you create the Hello World equivalent of an SSIS Project and deploy to the SSISDB, does that package work? – billinkc

3 Answers

0
votes

When you look at the connections in the SSIS package, did you put in a user id and password? If so, did you add a package password? The reason I ask is that if you don't add a package password, it will strip out things it thinks should be secure (like passwords). You might want to try putting the connection password in a configuration file, and add that file to the job. Either that or password encrypt the SSIS package but you will have to take special care when you run the package for the first time (or when you create the job) because you will need to provide the password that you used to encrypt the package.

0
votes

Reboot fixes this issue. Reason is because if you look at this message. Unable to terminate process 1850. That is a windows process. I would say reboot. If you really like to enquire before rebooting. You could use sysinternals process explorer. Download it and use that to find process id 1850 which may be hung process in windows.

Thanks Ali

0
votes

I have also faced this scenario many times but when I checked running the package manually,its completing successfully because I was using for each loop container and sequence container as well.In both cases for each loop and sequence were completing without validating other ones.So I checked precedence constraint and change it,Now it working and all the component ran successfully. Sometimes we miss to choose appropriate precedence constraint, there are many option like on Success ,failure,completion and then for you can choose values from Constraint ,Expression,Expression AND Constraint and Expression OR Constraint. Initially i was using Expression OR Constraint for success and now changed it to Expression AND Constraint, its working fine for me. You also need to do this,it will definitely work please try and let me know.