asked on

SSIS Package failed when run with SQL Agent task.

I am having a problem with an SSIS package that I am running with a scheduled task with SQL Agent. If I run the package manually with SQL Server Information Services it works, but when I automate the task with SQL agent it fails. I am using SQL Server 2008.
I created a VM to replicate my server in production with the same SQL Server version and the task with SQL agent does not fail, but in the other server fails. The server in production is running other tasks with the agent that runs successfully, but this new task fails.
Attached is a sample of the data the SSIS pkg is converting to fill out 4 tables: Employees, OU, EmployeeGroup, and Groups.
I also attached the error that I am getting.
I can figure out why the pkg runs successfully if I run it manually, but fails when is automated to run with the SQL agent.

Any help would be appreciated.
Sample.txt
Error.JPG

Jordan Clark

Normally, when you can execute a SSIS package manually, but it fails when scheduled it has something to do with permissions. BUT, based on your error message, it appears there is something wrong with the input file you're importing... So I'm not sure.

Is that input file dynamic or always the same based on when you run it manually/scheduled?

Alexa Cavill

ASKER

Hi Jordan,

The file I am using is changing frequently. The file is a list of employees that are active in Active Directory and it might change when we add or remove employees. I hope that answer your question.

Jordan Clark

Okay, I also see when you created a totally separate environment and tested - the scheduled task worked.

Are you sure the input file in question is exactly the same across all test scenarios?

Chris Luttrell

the permissions may be the problem. when the package is executed by the SQL Server Agent it is under the Service Account that is set up with, may not be the same as you or what the database is set up with. Since it seems you are writing out to a flat file, the user/account that the job runs as has to have access to that file directory. I am not a complete expert on these, always have to muddle my way through each particular issue, but we have also set some jobs and packages up to use a Proxy to overcome permission issues. You would have to look that up or get some real "expert" help if you have to go that far though, I don't know that off the top of my head.
Good luck, Chris

Alexa Cavill

ASKER

Hi guys,

Thanks for your comments.
Jordan, I am sure that the file I am using is the same.
Chris, the account I am using to run the package has access to the flat file. In fact, I am using a Proxy account to run the package.

Alexa Cavill

ASKER

Hello,

I checked everything you suggested, and after deleting and creating the job, I am getting a new error. The job still runs successfully if I run it from SSIS, but when I run it automatically using SQL Server Agent, it fails and gives me the error attached and the tables are empty.

Thanks for your help.
NewError.JPG

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.