Hi Urig,
I encountered the same problem, even on the same server upon deployment.
I ended up contacting Microsoft and opening a support case. After a couple
of hours on the phone, we found that if the SSIS Package's Security setting
"ProtectionLevel" was set to EncryptAllWithUserKey or
EncryptSensativeWithUserKey that the passwords would actually be lost. This
has to do with the fact that the SQL Server Agent process on your server is
running as a different user and cannot validate the user key basically. What
I ended up having to do is switch the Security ProtectionLevel to use
EncryptAllWithPassword or EncryptSensativeWithPassword and specify a
password for the package. I then re-deployed to SQL.

How I scheduled the Job also had to change. I could no longer specify my
package as a SSIS Step in a Job. I had to make my Job execute an "Operating
System (CmdExec)". The command line was :

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS
"\MSDB\YOURPACKAGEHERE" /SERVER Q /DECRYPT YOURPASSWORDHERE /MAXCONCURRENT
" -1 " /CHECKPOINTING OFF /REPORTING V

It seems like very much a work-around, but that's roughly the way I was told
to keep the protected passwords. My support case person spoke with the
engineers and that was the desired result evidently. They are working on
documenting the Security Levels more though, as this seems to be coming up a
lot. I honestly wouldn't be suprised if something in Security levels changed
in SP2.


Hope this helps.

-Matt Yeager




[quoted text, click to view]

Hello Matt,

Thank you very much for your reply. I haven't had time to test what you
suggest but it seems like solid advice and when I'll try it I'll post
here to let everyone know how it went.

Regards,
Uri Goldstein,
Israel

[quoted text, click to view]

how exactly do you do that ?

I'm having the same issue with SSIS & passwords

How exactly do you save the connection information in an xml file for
SSIS ?

Hi John

check this site out...

http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.h
tm



regards

nu1

-John

After you create the XML configuration file...edit the configuration
file by adding password=urpassword after the User ID =***

Urig, first we set up a service account to handle SQL Agent activities.
Once that was done, we did the following after learning that some of
methods do not work correctly. I have been saving my packages on the
file system of the 2005 SQL Server; scheduling the packages while logged
onto the actual server and set them up to use NT authentication. We have
had no success using any other method.

SQL DBA/ETL Developer from OHIO