With the new Project Deployment Model in SSIS developers are gaining the benefit of a new design time only feature called Project Connection Managers. If you’ve developed in SSIS previously Connection Managers require no further detailing, but for those new this is how SSIS connections to data that’s used as a source or destination. This new feature is visible in the Solution Explorer of SSDT (SQL Server Data Tools).
Below you will see the traditional method (Package Deployment Model) for developing package on the left, which has a similar option called Data Sources. Data Sources were purely a design time feature available in SSIS 2005 and 2008 to help manage connections to multiple packages. Basically you could change a Data Source created in the Solution Explorer and the next time you opened the package that used the connection it would update the metadata. Sounds nice but the key there is the update doesn’t occur until the next time you open it. So if you have hundreds of packages you would have to open all of them.
The real solution to that problem is to use configurations but I’d like to focus on the differences between the old Data Source method of doing things and the new Project Connection Manager method.
The image on the right is of the new Project Deployment Model and you quickly notice the Data Sources folder is not available. Instead you have Connection Managers which are similar in purpose but I think accomplish the job much better than Data Sources did. They’ve actually been made less complex if that’s possible because all you have to do as a developer is create a Project Connection Manager and then it’s automatically created in every one of your SSIS packages. As shown below you are able to clearly tell the difference between a Project Connection Manager and a regular Connection Manager that is only scoped to a single package.
If you have a regular Connection Manager that you would like to make a Project Connection Manager then you simply right-click on it and select to Convert it.
Another nice feature of Project Connection Managers is if you make a change to one the update applies to all the packages that use it without you having to open them each.
While this is a small new feature I thought it was pretty nice and worth spending some time discussing.