asked on

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?

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rainbow002

ASKER

I don't have a dadicated acct yet but any other suggestions?

chapmandew

do you have the error it gives?

Rainbow002

ASKER

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

Message
Executed as user: Servername\SYSTEM. The package execution failed. The step failed.

chapmandew

and Servername\SYSTEM has access to the db? what does the package do?

Steve Hogg

Copy the full message from the View History screen. Expand the job and highlight the expanded row. That is what we need.

Rainbow002

ASKER

builtin/admin has sysadmin rights on the server.
this package imports a table from another server.

chapmandew

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.

Rainbow002

ASKER

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?

chapmandew

you're not understanding....you're connecting to the OTHER server so you have to authenticate there.

Rainbow002

ASKER

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.

chapmandew

>>note, this job had been running fine until recently
what has changed?

Rainbow002

ASKER

it was giving error on one of the columns but after the package was saved with the new name, error disappeared. Nothing else changed