Connection Manager - Server Name not in list

[Ranvier]

Hi,

i am trying to create a new connection using connection manager for an SSID solution. However the server name does not appear in the Server name drop down list. I am configuring the package on my server and the database resides on the server that I am trying to connect to.

I'm using SQL Server 2005.

Any help would be appreciated as I can see all my other servers but not the actual one I need.

Thanks

 

[Ranvier]

Incidentally, I copied the SSID over from another server.

 

[mrdenny]

You should be able to simply type the server name in connection manager.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ranvier]

Yes, i created a new package from scratch and done a simple write to the database and it is fine so there is no problem connection to the actual server and database.

However... i have a further problem making a connection to my test server.

In my package I removed the original connection and created a new OLE DB connection using the configure connection manager. I selected New, Provider is 'Native LE DB\SQL Native Client', entered my server and database name (Test connection was good).

In the properties the new connection string is displayed correctly. However, when I build the package an error comes up about the connection.

Error loading x.dtsx: The connection "{5B0BBCC3-FCCF-4E55-A527-DC6E5080BD18}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

I don't know if this makes a difference but the ID in the properties of my connection is {C81FE6BF-640B-4F15-8572-8DA1CAEBB960} which is different from the one in the error message. It doesn't seem to like my new connection even though I tested it and it is OK.

Any ideas what I need to do.

I also have a number of other packages that have the same problem.

Thanks in advance

 

[MDXer]

Your error is because you deleted the old connection. SSIS relies heavily on GUIDS now your task can't find the data connection.

Open each task that is supposed to use this connection and select this connection.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Ranvier]

Thanks for the info.

I decided to keep the original connection in the package as the name of the connection is referenced in .dtsConfig files.

So after doing I changed the server name in the actual connection. I did this from the data flow tab in the Connection Managers sections (MS Visual Studio). Right clicked - Edit - changed the name of the server name to my test server and selected the database. Test connection was fine, and saved this.

Once I save this, the connection string changed to my current test server in the properties of the connection. However when I run the package I get the following error:

Error: 0xC0202009 at ReportsImport, Connection manager "myconnectionstring": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ".

Error: 0xC020801C at Myprocess import from file, BuyAtReportsImport [79]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "myconnectionstring" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Myprocess import from file, DTS.Pipeline: component "ReportsImport" (79) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Myprocess import from file, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Myprocess import from file: There were errors during task validation.

So even if I change the existing connection properties and save and Build my package again, my connection properties (connectionstring, servername etc) all revert back to its original data source when I re-open the package. Simply it does not seem to be saving the connection properties i put in.

Help is appreciated as i cant run any packages

Thanks

 

[MDXer]

you need to change the server info in your dtsconfig. The config settings override any you set at design time.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Ranvier]

That's very interesting. I built may package and then created a job from the build and executed it from the jobs folder in MS SQL Server management Studio and the job completed without errors and updated the database which is good news.

I'm a bit puzzled about the connection manager reverting back to its original server [Data Source] as this means I can't execute the package in Ms Visual studio which I would want to if I needed to debug or develop the package. When I execute the package I get

[BuyAtReportsImport [79]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "myconnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

so executing it from design mode still causes problems?

Any ideas?

Thanks

 

[MDXer]

As I said before if you are use package configurations and the connection config is incorrect.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Ranvier]

OK, so now what i have done - amended the original package and amended the dtsconfig file which includes the test server data source and is correct. Built the package and created a job. The job runs with success in SQL Server Management but not in design mode - so what is restricting it from running in design mode?

 

[Ranvier]

I think I must have been looking at my dtsConfig file too hard and overlooked one of the properties!

I updated the <ConfiguredValue>ServerName</ConfiguredValue> in dtsConfig to my new value and it works perfectly now.

Thanks for your input.