Solved

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

Posted on 2012-03-15
3
359 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:Rog
  • 2
3 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37728366
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
 
LVL 8

Accepted Solution

by:
Rog earned 0 total points
ID: 37729140
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
 
LVL 8

Author Closing Comment

by:Rog
ID: 37909661
No Solution was given that provided the correct answer.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now