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

Question

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

WilburSmith

Hall of Fame

Points: 3225
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 11 posts - 1 through 10 (of 10 total)

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

Phil Parkin SSC Guru Points: 246960 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: 3225 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: 246960 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: 3666 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: 3225 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: 246960 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

WilburSmith Hall of Fame Points: 3225 More actions · Answer 7

Hi,

I am not sure if I understand your point but...

Yes, the name of the connection manager has nothing to do with the server name inside the connection manager definition window.

I try to put it more clearly:

Project connection Manager

Server or filename (within the definition dialog): [Ville_ThinkBook\Ville_Sql2] does not work

Server or filename (within the definition dialog): Ville_ThinkBook\Ville_Sql2 works fine

BUT VStudio always adds those brackets after you have removed them and hit the OK, nice

Package connection Manager

Server or filename (within the definition dialog): [Ville_ThinkBook\Ville_Sql2] does not work

Server or filename (within the definition dialog): Ville_ThinkBook\Ville_Sql2 works fine

AND here VS Does Not add those brackets after you hit the OK, so no problem there

==>

There is an annoying bug or at least feature there...?

Ville

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

Thanks for clarifying. No idea where those square brackets are coming from ... I just played around with some connections and cannot reproduce what you are seeing. I even tried typing in exactly the same server\instance string (which of course I could not test).

Is your VS2022 bang up to date?

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: 3225 More actions · Answer 9

Hi,

Okay...that sounds strange...something must be different on our environments then.

I copied the Help About info of my VS22 Community:

Microsoft Visual Studio Community 2022

Version 17.11.5

VisualStudio.17.Release/17.11.5+35327.3

Microsoft .NET Framework

Version 4.8.09032

Installed Version: Community

Microsoft Visual Studio Tools for Applications 2022 00482-90000-00000-AA142

Microsoft Visual Studio Tools for Applications 2022

ASP.NET and Web Tools 17.11.231.19466

ASP.NET and Web Tools

Azure App Service Tools v3.0.0 17.11.231.19466

Azure App Service Tools v3.0.0

Azure Functions and Web Jobs Tools 17.11.231.19466

Azure Functions and Web Jobs Tools

C# Tools 4.11.0-3.24460.3+5649376e0e5f5db3743a94a62b073f2cce4be5d9

C# components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.

Common Azure Tools 1.10

Provides common services for use by Azure Mobile Services and Microsoft Azure Tools.

Cookiecutter 17.0.24138.2

Provides tools for finding, instantiating and customizing templates in cookiecutter format.

Microsoft JVM Debugger 1.0

Provides support for connecting the Visual Studio debugger to JDWP compatible Java Virtual Machines

NuGet Package Manager 6.11.1

NuGet Package Manager in Visual Studio. For more information about NuGet, visit https://docs.nuget.org/

Python - Profiling support 17.0.24138.2

Profiling support for Python projects.

Python with Pylance 17.0.24138.2

Provides IntelliSense, projects, templates, debugging, interactive windows, and other support for Python developers.

Razor (ASP.NET Core) 17.11.3.2442001+68650a7d94261bc56a1f4bc522c2ee35314b1abb

Provides languages services for ASP.NET Core Razor.

SQL Server Data Tools 17.11.47.0

Microsoft SQL Server Data Tools

SQL Server Integration Services 16.0.5685.0

Microsoft SQL Server Integration Services Designer

Version 16.0.5685.0

TypeScript Tools 17.0.30715.2002

TypeScript Tools for Microsoft Visual Studio

Visual Basic Tools 4.11.0-3.24460.3+5649376e0e5f5db3743a94a62b073f2cce4be5d9

Visual Basic components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.

Visual F# Tools 17.11.0-beta.24421.7+af2f522de602281d4ef5a7b71507c428e814c5c1

Microsoft Visual F# Tools

Visual Studio IntelliCode 2.2

AI-assisted development for Visual Studio.

Ed B Hall of Fame Points: 3666 More actions · Answer 10

Grasping at straws here, but is there any chance you have an expression overwriting the server name? It appears from the image that your connections do have expressions, but they could be anything. If there is not an expression for the server name, can you add one? If you leave the project connection pointing to the default instance and catalog, you could then set expressions either for server name and initial catalog or connection string in the package. I can't find anything online about named instances being a problem, only that backslashes work in package connection names, but not project connection names.