Considerations for packaging MSDE app

Hi
I have a C# Winform app which runs using SQL Server.
I want to package up the app, so that it deploys with MSDE.
Can anyone offer any pointers or references to help?
Thanks
Hi Paul,
I hope my suggestions will help you, because I have gone through pure h#$l
getting this to work right.
I have a VB.Net winforms app and I use Wise Installation System to install
the .Net framework, MSDE, the app.
A couple of things tried and failed:
1. Within wise, we installed MSDE with the command line option where it sets
the password. We decided to install MSDE into its own instance becasue we
don't want anyone coming behind us and hosing it up by installing an
untested service pack or anything.
2. At the end of the install, we run a batch file to stop and restart the
SQL service. In retrospect, this may not be necessary because of our
decision to reboot before the app is executed the first time.
3. We attempted to run a batch file to attach the database, but no matter
what we tried, it just would not work. It didn't matter if we rebooted and
then ran the batch, nothing worked. So, I added the code to my app that does
this:
a. check to see if the SQL service is running, if its not, warn the user and
shut down the app.
b. if this is the first time the app is executed:
b1. check to see if the database exists, of course it does not, so using
ado.net, execute the command against the master database to attach the
database. Test again to make sure the database is attached, if not, throw an
exception.
b2. Update my configuration file to show that the app has successfully
initialized.
4. Now here's the real McKoy - in order to get all this to work the first
time I connect to the database to check the version I had to use
POOLING=False in the connection string. Problem: The app ran as slow as a
turtle because now it wasn't using connection pooling anywhere. Solution:
Two connection strings, one for initial startup (first time connection) then
one for the rest of the time (without POOLING=false)
5. Another issue came up of how we would update our database, we surely
didn't want to have to detach and then re-attach another database and blow
peoples data away, so I added a version table to the database and I run a
check on the current version each time the app is executed. If the versions
are the same, I don't execute the update executable. So you may ask, how do
you know if you have a new database version and an update needs to take
place? Answer: When a patch is applied (or an executable update) I have a
Version.xml file where we will put the new version number to be compared
against. The executable that runs the update is also new since it will have
code in it to do the update.
But, here's the big question, after you install MSDE, do you need to reboot
or not? The answer is that I was able to get my app to work without having
to reboot, but we have decided to require a reboot because I just believe
there are things in MSDE that need to be set correctly and I think rebooting
is the only way to do it. Again, my app works without it, but I just feel
like its more proper to reboot. I mean after all, you are installing the SQL
desktop engine which is no minor thing, not to mention the .Net framework
install.
Now, there are probably many MSDE experts out there reading my steps
thinking, man, he did this totally wrong, but I'm telling YOU these are
things you are in the end, probably going to have to do to (especially the
POOLING = False setting).
There are many things we haven't even gotten to yet like:
1. What happens if a person doesn't reboot the machine, how do you stop them
from running the app?
2. What if a person runs the install, uninstalls, then reinstalls and
reboots, how does that affect the app?
3. Do you want the uninstall to uninstall the .Net framework and MSDE? I
personally don't think so but then both these items are rather large.
Hope this helps.
STom
"Paul Aspinall" <paul@.aspy.co.uk> wrote in message
news:ctuKd.9606$n9.2569@.fe3.news.blueyonder.co.uk. ..
> Hi
> I have a C# Winform app which runs using SQL Server.
> I want to package up the app, so that it deploys with MSDE.
> Can anyone offer any pointers or references to help?
> Thanks
>

Connector between executables dissapears

Dear Forum Members.

I am currently using Visual Studio 2005: Business Intelligence Development Studio to create an Integration Services Package.

The package contains several containers, within each container there is a ‘foreach loop container’ and within the ‘foreach loop container’ there is a script component, execute component and another script component.

Each of the three components is connected together by single connectors.

My issue arises when I disable a container and then enable the container; the connectors between the components disappear (e.g. are not visible) but do exist as I can not create another connector between the components. When I attempt to create the connector again between the components I receive the error message. This tells me the connectors are present but not visible.

TITLE: Microsoft Visual Studio

Cannot create connector.
Only one workflow can exist between the same two executables.

BUTTONS:

OK

Can someone kindly explain why this is happening and how to resolve this issue?

I can not change the connector types from success to completion or vice versa because the connectors are not visible for me to select them.

Many Thanks

I have had this happen quite frequently. The only solution I found was to exit and restart BIDS.|||

Dave

Thanks very much for your response. The resolution works.

Regards

sqlsql

Connection Strings

I have created a SSIS package, and got it to perform as I want.

I am having a problem though with connecting to my data source (OLE DB Source) using SQL Server Authentication.

If I use Integrated security, I have no problems. If I use SQL Server Authentication, I get errors.

Here is the connection string that works:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Integrated Security=SSPI;Initial Catalog=MyDataBase

Here is the connection string that fails:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Persist Security Info=True;Password=**********;User ID=myUserid;Initial Catalog=MyDataBase

The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.

Here is the error I am getting:

[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.

[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.

I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.

It seems I am missing something really basic here, but am not seeing it.

Any thoughts?

Has this ever worked for you? Are you able to create a brand new package and it still fails? Are you using package configurations?|||

Has this ever worked for you? Yes, using integrated security

Are you able to create a brand new package and it still fails? No, actually I tried a new simple package, and it works.

Are you using package configurations? No

I think the key here is your second point. I created a new package, and the connection now works. So, the question is how do I fix my original package? I'm not really thrilled about creating a brand new package being that this one is quite complicated.

Suggestions?

|||Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.|||

WRBehning,

Welcome to the new world of SSIS! I have had similar problems, especially when you copy and paste items. Basically I think the internal Ids in SSIS get messed up somehow. Alot of times if you delete and re-add an item it fixes the problem. Doesn't give me great confidence when an "enterprise solution" has so many strange problems. Good Luck! (to us all)

|||

WRBehning wrote:

Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

If you have a RFE (Request for Enhancement) then file it at http://connect.microsoft.com with a detailed write-up.

Only thru doing this will SSIS get closer to being the product that people want it to be.

-Jamie

|||

WRBehning wrote:

Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

Can you explain exactly what the problem was in the original. Its not clear from this?

If its something that people need to know then I'd like to add it here: http://blogs.conchango.com/jamiethomson/archive/2006/10/18/SSIS_3A00_-Random-information-for-beginners.aspx

Thanks

-Jamie

|||The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.|||

WRBehning wrote:

The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.

That's worrying if it didn't work. However I have previously had no problems with changing connection strings (for that is what we're talking about here).

One thing that can cause problems is if (for example) the ServerName property doesn't marry up with the server name that you are setting in the ConenctioNString property although in your case the only difference is in authentication mode so I shouldn't expect it to be a problem.

I've also tried your repro steps as above and didn't experience any problems.

What version/service pack are you on?

-Jmie

|||

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

|||

WRBehning wrote:

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

Seems we're on the same. Strange.

Oh well. Let's chalk it up to experience. If it happens again, let us know.

Thanks

Jamie

|||

I am experiencing the same behaviour.

The package was first created using EncryptWithUserKey setting, and the connection credentials was for a trusted connection to a SQL Server.

We changed the package security to use EncryptWithPassword, and the connection credentials stayed the same and continued to work properly.

When we changed the connection credentials to use a Standard SQL login, I would set the login and password and check the "Remember password" option. The Test Connection button was successful. Then after closing the connection manager properties, I would immediately reopen the properties and the password was gone, but the checkbox was still there, and the Test Connection would fail.

Mike

Connection Strings

I have created a SSIS package, and got it to perform as I want.

I am having a problem though with connecting to my data source (OLE DB Source) using SQL Server Authentication.

If I use Integrated security, I have no problems. If I use SQL Server Authentication, I get errors.

Here is the connection string that works:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Integrated Security=SSPI;Initial Catalog=MyDataBase

Here is the connection string that fails:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Persist Security Info=True;Password=**********;User ID=myUserid;Initial Catalog=MyDataBase

The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.

Here is the error I am getting:

[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.

[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.

I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.

It seems I am missing something really basic here, but am not seeing it.

Any thoughts?

Has this ever worked for you? Are you able to create a brand new package and it still fails? Are you using package configurations?|||

Has this ever worked for you? Yes, using integrated security

Are you able to create a brand new package and it still fails? No, actually I tried a new simple package, and it works.

Are you using package configurations? No

I think the key here is your second point. I created a new package, and the connection now works. So, the question is how do I fix my original package? I'm not really thrilled about creating a brand new package being that this one is quite complicated.

Suggestions?

|||Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.|||

WRBehning,

Welcome to the new world of SSIS! I have had similar problems, especially when you copy and paste items. Basically I think the internal Ids in SSIS get messed up somehow. Alot of times if you delete and re-add an item it fixes the problem. Doesn't give me great confidence when an "enterprise solution" has so many strange problems. Good Luck! (to us all)

|||

WRBehning wrote:

Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

If you have a RFE (Request for Enhancement) then file it at http://connect.microsoft.com with a detailed write-up.

Only thru doing this will SSIS get closer to being the product that people want it to be.

-Jamie

|||

WRBehning wrote:

Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

Can you explain exactly what the problem was in the original. Its not clear from this?

If its something that people need to know then I'd like to add it here: http://blogs.conchango.com/jamiethomson/archive/2006/10/18/SSIS_3A00_-Random-information-for-beginners.aspx

Thanks

-Jamie

|||The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.|||

WRBehning wrote:

The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.

That's worrying if it didn't work. However I have previously had no problems with changing connection strings (for that is what we're talking about here).

One thing that can cause problems is if (for example) the ServerName property doesn't marry up with the server name that you are setting in the ConenctioNString property although in your case the only difference is in authentication mode so I shouldn't expect it to be a problem.

I've also tried your repro steps as above and didn't experience any problems.

What version/service pack are you on?

-Jmie

|||

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

|||

WRBehning wrote:

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

Seems we're on the same. Strange.

Oh well. Let's chalk it up to experience. If it happens again, let us know.

Thanks

Jamie

|||

I am experiencing the same behaviour.

The package was first created using EncryptWithUserKey setting, and the connection credentials was for a trusted connection to a SQL Server.

We changed the package security to use EncryptWithPassword, and the connection credentials stayed the same and continued to work properly.

When we changed the connection credentials to use a Standard SQL login, I would set the login and password and check the "Remember password" option. The Test Connection button was successful. Then after closing the connection manager properties, I would immediately reopen the properties and the password was gone, but the checkbox was still there, and the Test Connection would fail.

Mike

Connection string from outside

Hi there!!

I want to make a package and pass connection string from outside say through .ini file, so that I can put package on any system and use some external source to pass connection string.

How do we do this in SSIS?

Rahul Kumar, Software Engineer, India

Hi,

Package Configurations in SSIS are used for the same purpose that you are looking for. Package config can be used to pass on the values to the package at run time. For more details, please check http://msdn2.microsoft.com/en-us/library/ms141682.aspx

Let me know if this solves your issue.

Thanks,

S Suresh

|||

Hi Suresh

Yeah this is fine,we can assign configuration settings but still I am scratching my head how to change connection string in connection manager

|||

If you define a configuration, this is held as part of the package definition. In yoru case you would define the configuration such that the property value to be set is the ConnectionString of the specified connection.

When the package loads the config is processed, so your new connection string is read for the specified location and applied. That loaded instance of the package will have the new connection string on the connection manager.

Still got an itch? If so can you explain why you do not think thsi solves the issue?

|||When you go through the Package Configurations Wizard choose the ConnectionString property of the ConnectionManager you want to configure. At runtime it will try and configure that property for you. You don't have to manually set it.

Connection Problems to Oracle Database

Hi,

I have a package connecting to oracle database ,this was developed on 32 bit Windows OS now i moved the same to 64 bit OS and it fails to connect to oracle database.

I have created system DSN and tested it was successfull.

In my package i am using ado.net connection object ,I am using ODBC data provider.I select the Data source in the drop down .provide user name and password and when i hit on test connection it fails with the following message

Test connection failed because of error in initilizing the provider error[08004]

[oracle][odbc][ora]ORA 12154 : TNS could not resolve the connect identifier specified

[Error]IM006[Microsoft][ODBC driver manager] drivers SQL setconnectattr failed

Any help on this will be appreciated

Note:- even though the OS is 64 bit i am using 32 bit ODBC as BI studio can only access 32 bit odbc

32-bit ODBC drivers won't run with 64-bit SSIS. You either need a 64-bit ODBC driver for Oracle (not sure if one exists) or run the 32-bit version of SSIS on the 64-bit box.|||Initially i had installed 64 bit oracle drivers and had created sytem dsn in 64 bit odbc but when i tried accesing the DSN(from drop down in connection object) i created in ado.net connection object i was not able to do so , as using the BI studio you can only access 32 bit odbc for which i had to install 32 bit oracle drivers after creating the dsn in 32 bit odbc i was able to access the DSN but when i did the test connection it failed.with above error message.

Connection problem when deploying

Hi all,

Here is my problem, I know there are already Posts about that but I still run after the same problem ...

I have a package with a connection in the connection manager. I use the xml configuration file to adapt my package when deploying it on the production server. In the xml file, I chose to modifiy the Connection String, the user name and the password.

I set the ProtectionLevel propertie to "DontSaveSensitive".

But when I deploying and scheduling my package on a other server, the execution failed because it can't Acquire the connection.

Did I miss something else in the configuration or in the properties ?

Thank you

Are you sure you did a server restart to refresh the environment variabe on the other server?

I use environment variables to tell where is the xml config but it looks like that if I change the xml, even though the variable still resolves to the config file, the updated values are still unavailable, untill I restart the server.

I do not like it, I have to restart a server that should not be restarted but, that the only way i could get it to work.

Philippe

|||Hello,

No I didn't restart my server but I don't have to because I'm not using environment variable.

Connection problem when deploying

Hi all,

Here is my problem, I know there are already Posts about that but I still run after the same problem ...

I have a package with a connection in the connection manager. I use the xml configuration file to adapt my package when deploying it on the production server. In the xml file, I chose to modifiy the Connection String, the user name and the password.

I set the ProtectionLevel propertie to "DontSaveSensitive".

But when I deploying and scheduling my package on a other server, the execution failed because it can't Acquire the connection.

Did I miss something else in the configuration or in the properties ?

Thank you

Are you sure you did a server restart to refresh the environment variabe on the other server?

I use environment variables to tell where is the xml config but it looks like that if I change the xml, even though the variable still resolves to the config file, the updated values are still unavailable, untill I restart the server.

I do not like it, I have to restart a server that should not be restarted but, that the only way i could get it to work.

Philippe

|||Hello,

No I didn't restart my server but I don't have to because I'm not using environment variable.

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

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