how to schedule job for SSIS package?

I'm having difficulty running package inside a job. When I run the package manually from SSIS it runs fine but when I schedule job it fails. It looks like securiy/permissions but I'm using localsystems acct to run sqlserver agent acct which is part of sysadmin group.
What am I doing wrong here?
Rainbow002Asked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
run SQL Agent under a domain account...
0
 
Rainbow002Author Commented:
I don't have a dadicated acct yet but any other suggestions?
0
 
chapmandewCommented:
do you have the error it gives?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Rainbow002Author Commented:
I don't see any details but here's error:

Message
Executed as user: Servername\SYSTEM. The package execution failed.  The step failed.
0
 
chapmandewCommented:
and Servername\SYSTEM has access to the db?  what does the package do?
0
 
HoggZillaCommented:
Copy the full message from the View History screen. Expand the job and highlight the expanded row. That is what we need.
0
 
Rainbow002Author Commented:
builtin/admin has sysadmin rights on the server.
this package imports a table from another server.
0
 
chapmandewCommented:
make sure servername\system has rights on the sql server OR create an account on the server and run the service under that account

since you're connecting to a different server, that server will try to authenticate the service on that machine....that is why the domain account comes in so handy here.
0
 
Rainbow002Author Commented:
Tried running the job using my domain acct that is part of sysadmin group but still no luck. Seems like job fails while connecting to SSIS. Won't sysadmin have full rights to SSIS?
0
 
chapmandewCommented:
you're not understanding....you're connecting to the OTHER server so you have to authenticate there.
0
 
Rainbow002Author Commented:
Well that's what I'm doing inside the package where I have added two connections for source and destination server that's I guess why I'm able to run the package when I right-click and execute package manually...

Also, I tried another approach: Connected to SSIS from SSMO. Under MSDB, I located this package and tried to execute, it fails just like the job does.

note, this job had been running fine until recently. Now after fixing when I run the same job it fails.
0
 
chapmandewCommented:
>>note, this job had been running fine until recently
what has changed?
0
 
Rainbow002Author Commented:
it was giving error on one of the columns but after the package was saved with the new name, error disappeared. Nothing else changed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.