Visual Studio 22 / SSIS Project / (Project) Connection problem

Question

Visual Studio 22 / SSIS Project / (Project) Connection problem

WilburSmith

Hall of Fame

Points: 3222
More actions
October 31, 2024 at 3:20 pm

#4475966

Hello,
First of all, I find it odd/annoying that I can't exclude a Project level connect from a package Connection Managers... at least I can not do it. Why should they show / fill the space in packages with nothing to do with those connections.
But even more annoying is that when you have a project level connection manager against a NAMED Sql Server instance - like MySqlServer\Instance2 - visual studio forcefully updates the name as [MySqlServer\Instance2]
...and then the connection will not work! If you remove the brackets, the Test connection is successful but there is no way to save it like that...
This does not happen with package level connection managers.
I hope I have just understood things wrong or just can't use the tool properly.
Ville

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246936 More actions · Answer 1

First of all, I find it odd/annoying that I can't exclude a Project level connect from a package Connection Managers... at least I can not do it. Why should they show / fill the space in packages with nothing to do with those connections.

They don't 'fill the space in packages'. Try opening one of your packages in an XML editor and you'll see that unused project connections do not appear and are not referenced.

But even more annoying is that when you have a project level connection manager against a NAMED Sql Server instance - like MySqlServer\Instance2 - visual studio forcefully updates the name as [MySqlServer\Instance2]
...and then the connection will not work! If you remove the brackets, the Test connection is successful but there is no way to save it like that...

You can rename the connection manager without adjusting the server name!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

WilburSmith Hall of Fame Points: 3222 More actions · Answer 2

Hi Phil,

Yes, I opened the XML's as well and saw that they are not there but in the Connection Managers window they are...

"You can rename the connection manager without adjusting the server name!"

I know that but how does that help with the problem I described?

Ville

Phil Parkin SSC Guru Points: 246936 More actions · Answer 3

WilburSmith wrote:

Hi Phil,
Yes, I opened the XML's as well and saw that they are not there but in the Connection Managers window they are...
"You can rename the connection manager without adjusting the server name!"
I know that but how does that help with the problem I described?
Ville

They are in the CM window because they're available to you within the project. If you are designing packages for which the project-level connection managers are not relevant, perhaps you should create another project?

The second problem you described I clearly do not understand. I have a named local instance which I access from SSIS without an issue. Can you please expand on what you are trying to achieve?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ed B Hall of Fame Points: 3658 More actions · Answer 4

Can you not leave the server name alone, but change the connection manager name to remove the square brackets and the backslash, which is an illegal character?

WilburSmith Hall of Fame Points: 3222 More actions · Answer 5

Hi,

Yes, but if I was to decide, I would change the functionality so that you could drag/drop the project connection to the package when it is needed. At least in my case there are several packages that use the same connections which justifies the need for project connection but then again there are many packages/project areas that only need "one time connections", that is, then package connection makes sense. But this is just my opinion and I should argue it with the VStudio SSIS team instead:)

"The second problem you described I clearly do not understand. I have a named local instance which I access from SSIS without an issue. Can you please expand on what you are trying to achieve?"

Like said, nothing special, other than that the named instance does not work when set in PROJECT Connection manager.... see my text and pics.

Ville

Phil Parkin SSC Guru Points: 246936 More actions · Answer 6

WilburSmith wrote:

Like said, nothing special, other than that the named instance does not work when set in PROJECT Connection manager.... see my text and pics.
Ville

I've looked again at your pics and they clearly show that you have edited the server name. That, of course, will cause the connection to fail, regardless of it being a package or project connection.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin