SSIS Project Connection Managers

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.

Package Deployment Model Project Deployment Model
image image

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.

image

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.

image

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.

Advertisements

4 comments

  1. Devin,

    Its taken me a while to get caught up on the SSIS 2012 but this blog brought up an interesting point. I remember many issues with the old Data Sources or moreso the Data Source Connection Manger that used to be available.

    In your blog you state “The real solution to that problem is to use configurations” to which I whole heartedly agree. What I was a bit confused on though while from a design time perspective this Project connection manager seems like an improvment, is it an improvement from the standpoint of applying configurations in different enviornments? Is there any concerns or issues that should be taken into account when using SSIS configurations with the new Project connection manager?

    Thanks!

    1. So once you’ve upgraded to SSIS 2012 you no longer have configurations as an option. They have been removed from the tool and replaced with a combination of parameters and environments. You can use these environments to configure the project connection managers. Here’s a good post that sums up the new way of managing this. http://sqlblog.com/blogs/jamie_thomson/archive/2010/11/13/ssis-server-catalogs-environments-environment-variables-in-ssis-in-denali.aspx

  2. It’s interesting how you said that when you open the package now it updates the metadata. This would be a great way of being able to stay up to date on all the projects you are working on as well as getting a sort of SSIS custom service tailored specifically to your needs. The most important thing is being able to stay on top of your projects and make sure that you know everything you need to know about them.
    https://rftclouds.com/ssis_custom_tasks.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s