I build a simple SSIS package. There are only one connection "Remote
DB" and one SQL task.
the connection's string is such like "Data Source=RemoteSvr;User
ID=sa;Initial Catalog=demo;Provider=SQLNCLI.1;Persist Security
Info=True;", where RemoteSvr is a machine different from the machine
the package deployed. The SQL task will aquire the connection to create
a test table.
I succeeded in Management Studio, but failed in a simple web site which
running the package. The error is "Failed to acquire connection "Remote
DB". Connection may not be configured correctly or you may not have the
right permissions on this connection."

Could anyone explain why I got such problem?
Another question is why I can not save the password in the connection
string? how can I do that?

That is actually the error, the password is not saved when you deploy the
package and use from asp.net. There are alot of consderations so let's
start:

The only way to be able to save the a password and for the package to
actually read it is using a configuration file, the problem would be that
the pwd will not be encrypted if that works for you, then your are done!.
Another way is to use integrated security for your connecion and that means
that you need provide access to your asp.net user to de DB and if the
package was deploy to SQL to the package itself, if deploy to Filesystem
then to the directory.

All is realted with the ProtectionLevel property of the package. Here are
all the links I used to learn about it:

http://groups.google.com/group/micro...8629239a69381f
(explain)
http://groups.google.com/group/micro...29241b027467e0
(explain)
http://groups.google.com/group/micro...82d519d4be6cc5
(solutions)
http://forums.microsoft.com/MSDN/Sho...94092&SiteID=1
(solutions)

http://msdn2.microsoft.com/en-us/library/ms198485.aspx (about making
configurations files for packages)

One thing that might be worthy to consider is that you can not share
configurations files among packages (AFAIK), so if you have the same
connection in all your packges your'll need to modify it in all the config
files.

I hope this helps
George.



"Elvis Tang" <headsea2005 (AT) gmail (DOT) com> wrote