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!
[quoted text, click to view] Sienna wrote:
> 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
