SQL Server 2005 (SSIS Package / Password not saving on datasource

I have a rather simple ssis package.  It has two datasources.  One local and one remote.  

I have Visual Studion 2005 installed on the SQL Server.  (Server is 2008 R2)  SQL 2005.

I can run the package just fine in Visual Source Safe, but when I deploy the package and run it from inside SQL Server (Job or Manually run the package) I get an error about the remote connection not haveing a password.

I have tried to change the protection level to server storage then saving the package but I get an error The system could not verify that the destination supports secure storage capability.

I have tried Encrypt all with password, but it won't save the password.

Any suggestions?

Thanks,

Roger
LVL 8
Rog DManager Inforamtion SystemsAsked:
Who is Participating?
 
Rog DConnect With a Mentor Manager Inforamtion SystemsAuthor Commented:
The issue is the password for the database connection is not staying in the package.  The package WILL NOT let me save the connection password.

Well when I import the package into SQL Server to run the password is missing.

When I import as a "FileSystem" the password is missing.

But when I run it manually in Visual studio, the password is there and it runs fine.

Thanks,
0
 
Simon BallCommented:
in a job you can specify the run as password.

when i ran into this problem, i had to get the server guys to give me an account with same username and password to set it to run as, or use an account which had relevant permissions to access database through windows auth on both servers.

there is also some way to do this by adding credentials for windows accounts to run as server security under security/credentials:

http://msdn.microsoft.com/en-us/library/ms190703.aspx
0
 
Rog DManager Inforamtion SystemsAuthor Commented:
No Solution was given that provided the correct answer.
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.