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 DSQL Developer / Web Development / Business AnalysisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Simon BallChief information OfficerCommented:
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 DSQL Developer / Web Development / Business AnalysisAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rog DSQL Developer / Web Development / Business AnalysisAuthor Commented:
No Solution was given that provided the correct answer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.