I wrote and deployed an SSIS package as a file system package (SQLServer
2005). I tried scheduling the package to run as a job step using SQL Agent
Service Account with Windows Authentication and I have admin rights. It
fails. I have tried a lot of things to get this job to run the package but
to no avail.

I then tried using the dtexec utility with the file path and I changed the
security protection level to EncryptSensitiveWithPassword and provided the
password in the command line. This works. But I have to supply the
password in the command line which I do not want to do.

Could someone:

A. explain why I cannot run my package as a job step
B. if I have to use the command line utility how I can get away from
providing the package password in the command line

Much thanks,
Sienna

I would speculate that this type of problem 2005 or otherwise are
nearly always related to what credentials are being used for the "SQL
Agent".

I would guess you may have to use that login when you publish/deploy
the package instead of your own; my guess is that even if both set of
crendials are admins - it would not allow the second admin (agent) to
open / execute / use the package which was encrypted (using first
admin). In fact I might even speculate the the key might be different
between servers so saving on one instance and opening on another might
not work - though I would have hoped they immplemented that feature
with the "domain user" account in mind.

Oh - on command line clear text passwords - put the command line into a
..bat file and then deny all rights to that file but to appropriate NT
GROUP.

Report back your findings/resolve!

Sienna wrote:

Ah - better yet there are tons of options under the package properties,
ProtectionLevel - did you consider changing those as a solution?