Connection problem to other server.

Hi,

I have a SSIS package on a SQL SERVER 2005 and it is importing data from SQL server 2000 which is running on other server SERV03. when i tried to run the package i got the following error

failed to connect to server SERV03.

when i run this package on my local machine under BIDS it works fine. so i think so it is not a connection problem.

any suggestion. because i am unable to understand which thing is going wrong.

Regards,

Haroon

Where are you running this from now and how are you running it?|||

i have deplyed this package to SQL SERVER 2005 and and i am runnign it from msdb using Management studio.

Thanks

|||

You will need to set up the proxy / credential / etc...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1

|||

really Thanks for your help.

i have looked these links. thses links are about running package from agent job and i am running packge form management studio manually. and there are i have other packages which are executing fine but they they are not connecting any data source outside of server. but this package is trying to connect to other server. if you need to ask any other question please let me know.

Regards,

Haroon

|||Perhaps there is a firewall or something that prevents you to connect to the "other server" from the server where your packages are stored.|||

Hi, i have chekced the issue . it is not a problem of firewall. but i have find one more thing. when i have looked packges properties from management studio under connections tab i have found my connections. the one connection which is suppose to connect to outside server is there

SqlServerName=SER03;UseWindowsAuthentication=False;UserName=sa;

the thing is it is missing password in there. which is strange becasue i have specified the password in my package developement.

is there any suggestion it is a normal behavior or something else.

|||

SSIS does not store viewable password information.

|||

Ok, I think I have faced a similar problem with passwords; it may or may not apply in your situation.

First, in my situation I run the package using an agent. Second, I am using an ODBC connection and was having problems getting the password to be stored. This is what I had to do.

Upon configuring the SQL Agent Job I had to edit the script of the job in a query editor window and manually insert the text password=xxxx after the DSN string. I then executed the query (after also editing the job name) and created another agent job, but now the password is stored. If I went into the SQL Agent job again and edited the DataSource tab for the package I once again lose the password and need to go through all the steps again.

Now, in my situation when I ran the package via Mgmt. Studio but outside an agent it ran fine; the problem I had was with the agent.

Again, I am not sure if this is going to help in your situation.

|||

You may want to check the protection level of your package:

http://technet.microsoft.com/en-us/library/ms141747.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=908864&SiteID=1