4

I wanted to know the utility of carrying out a conversion in project connection in the connection managers in ssis

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
Asma
  • 51
  • 2
  • 3
    When we convert the package connection to project connection, same connection is shared across all packages. This allows caching the information and reuses in multiple packages which will improve performance. https://blogs.msdn.microsoft.com/mattm/2011/07/19/project-connection-managers/ – Aashish Jain Apr 03 '17 at 18:33
  • 2
    @AashishJain you can write this as an answer – Hadi Apr 04 '17 at 04:22

2 Answers2

1

Project-level connection managers allow you to define the data source connection once and use it in all packages that are part of the project.

You can create a new project connection by right-clicking on the Connection Managers node shown above,

enter image description here

Project-level connection managers allow you to set up a connection manager on the project level. This means that you can define a connection for the whole project and use it in all packages that are part of the project.

Mr. Bhosale
  • 3,018
  • 1
  • 17
  • 34
0

When you convert a package connection to a project connection it can be used by any number of packages in the project without having to manage changes to them individually. This has a couple of advantages:

  1. You can see how many different connection your project has from looking at the project and not having to open and look at every package.
  2. You can change a connection string in your project connection manager and know it will make all the packages work towards the new source or destination.
  3. You can make the project connection manager's connection string a project parameter, allowing the entire project to be reconfigured without redeployment by just changing the parameter sent in when executing any package using the connection.
  4. To build further on 3, you can set up one or more environment configuration to the deployed solution allowing you to select the parameter to be sent to the project during execution and have the same package work different environments without redeployment while also keeping track of which configuration does what.

For example in one of my installation if some source system decides to move to a new server, I just open my production configuration and paste in the new connection string for the value that is connected to the affected project parameter representing the connection string of the project connection. I can still keep the testing configuration since it has a different source which hasn't moved.

David Söderlund
  • 978
  • 8
  • 14