Project Connection Managers

Project Connection Managers

Rate This
  • Comments 5

Today’s post is by Sergio Clemente Filho – a developer on the SQL Server Integration Services team.

--------------------------------------

 

Simple Project Connection example in BIDS

One of the first new things you will notice in the solution explorer when you create a new SSIS project (opening existing SQL Server 2008 R2 or previous versions will not show this node, unless you convert the project to “Project Deployment Model”) is the “Connection Managers” node (See Figure 1). This is a new feature in Denali that allows sharing connection managers across multiple packages.

clip_image001

Figure 1 - Solution explorer

To create a project connection manager, right click on the “Connection Managers” node and click on the “New Connection Manager” option (as seen in Figure 2).

clip_image002

Figure 2 - Creating new project connection manager

This will prompt an existing familiar dialog to choose the connection manager type, then the connection manager information as it can be shown in figures Figure 3 and Figure 4 respectively.

clip_image003

Figure 3 - Select connection manager type

clip_image005

Figure 4 - Configuring connection manager

After the project connection manager is created, it will automatically appear in both solution explorer and connection manager list view as it can be shown on Figure 5. Currently project connection managers are being shown in bold but this might change before RTM.

clip_image007

Figure 5 - After creation

Once the project connection manager is created, it becomes available for being used similar to how package connection managers are used. An example is given below with an Execute SQL Task in Figure 6:

clip_image009

Figure 6 - Using project connection managers in SQL Task

The package should successfully run as shown in Figure 7 .

clip_image010

Figure 7 - Running in BIDS

 

Promoting and Demoting project connection managers

Project connection managers can me demoted to package connection managers as can be shown below in Figure 8. Once a project connection manager gets demoted all other packages that use this project connection will have their reference broken.

clip_image011

Figure 8 - Demoting a project connection

You can also promote a package connection back to a project connection manager by right clicking on the package connection and choosing the option “Convert to Project Connection”

 

Note: Is worth noting that all operations on project connection managers do not participate in the undo transaction. This is true for creation, deletion, editing, promotion and demotion of project connection managers. This is unfortunately a by design behavior because undo cannot span across different documents.

 

Creating project connection managers programmatically

Let’s now see how to use project connection managers programmatically. Table 1 shows the code to create a project connection manager and access the newly created connection from the package Connections collection.

· Line 8: Creates a project

· Line 9: Creates an OLEDB project connection with the stream name “Connection.conmgr”. The two arguments of the ConnectionManagerItems.Add are explained below:

o Creation name: The connection type of the connection manager, examples are: ADO, ADO.NET, FILE, FLATFILE, HTTP, etc. This is the identical creation name used in Connections.Add (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.connections.add.aspx)

o Stream name: An unique file name that ends with the suffix “.conmgr”. The name cannot have more than 128 characters.

· Line 10: Sets the name of the underlying runtime object. cmi.ConnectionManager is a reference to a ConnectionManager object (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.connectionmanager.aspx)

· Line 12-14: Creates a package and adds to the project

· Line 16: Accesses the project connection manager from the package connections. One thing worth noticing is that the project connection managers will automatically appear in the Package.Connections connections. This is why it automatically appeared in the existing UIs without any effort.

Table 1 - Creating SCM programmatically

  1. using System;
  2. using Microsoft.SqlServer.Dts.Runtime;
  3. namespace SCMSample {
  4. class Program {
  5. static void Main() {
  6. using (Project proj = Project.CreateProject()) {
  7.             ConnectionManagerItem cmi = proj.ConnectionManagerItems.Add("OLEDB", "Connection.conmgr");
  8.             cmi.ConnectionManager.Name = "HelloWorldSCM";
  9.             Package package = new Package();
  10.             proj.PackageItems.Add(package, "Package.dtsx");
  11.             Console.WriteLine(package.Connections[0].Name);
  12. }
  13. }
  14. }
  15. }

Note: There is one caveat though, if a package contains the same name as the project connection, the package connection will take precedence over the project connection (Similar to in a programming language when a local variable contains the same name as an attribute). You can make the project connection visible by either renaming either connection, or by deleting the package connection.

 

More advanced example of project connection managers

One important concept of project connection managers is that the same object is shared across all packages. This allows caching the information and reuse in multiple packages which will improve performance. For the next example I will quickly show how a cache connection manager can be used to share information across two child packages.

Imagine I have the following parent package as it can be seen in Figure 9:

- Contains a data flow that populates a cache connection manager that is at project scope.

- Executes two child packages (Child1, Child2)

clip_image013

Figure 9 - Parent Package

The data flow of the parent it’s pretty straightforward and it’s shown in Figure 10. The OLE DB Source retrieves all columns from the table Person from AdventureWorks database and the cache connection managers will contain all columns and will index FirstName and LastName with indexes 1 and 2 respectively.

clip_image014

Figure 10 - Data flow that populates the cache

clip_image016

Figure 11 - Cache connection manager

Once this is done, the child packages can reference the project connection manager named “Shared CCM” and use them.

clip_image017

Figure 12 - Child package 1

In the lookup transform, make sure to select “Cache connection manager” as the connection type as it can be seen in Figure 13 and select the connection “Shared CCM” manager in the “Connections” tab as it can be seen in Figure 14.

clip_image019

Figure 13 - Cache connection manager UI 1

clip_image021

Figure 14 - Cache connection manager UI 2

 

Summary

Hope that was a useful overview of project connection managers, we saw how to create the project connection manager from BIDS and from API. We saw that project connection managers will show automatically in existing UIs (Unless the name collide with a package connection) so you can use them as it was a normal connection manager. We also saw a more advanced example where the project connection manager was used in order to fetch the information only once through the cache connection manager.

 

Known issues

· Expressions are not supported on project connection managers. BIDS will hide the expressions option in the property grid.

· Logging might not always work. There might be scenarios where if you log on a project connection manager the logging won’t appear in the package logging

· If you try to click on “Parse Query” on SQLTask you will get a “Specified cast is not valid”

These existing issues should be addressed before RTM

Leave a Comment
  • Please add 2 and 1 and type the answer here:
  • Post
  • Excellent article.

    Will this undo feature in project connection available in RTM?

  • This is one of the most anticipated enhancement that I like. Kudos to the SSIS Team.

  • Do project level shared connection managers support dynamic configurations?  For example, we might want to share a connection across our packages but point to a certain server for development and a certain server for production.

  • I am using Microsoft.SQlServer.management.IntegreationServices namespace to programmatically run my SSIS 2012 packages.  Everything seems to work great but I have a requirement to dynamically set my connection strings at runtime.  I know there are easy ways to do this using Microsoft.SqlServer.Dts.Runtime because there is a Connections property on the Microsoft.SqlServer.Dts.Runtime.Package class.  There seems to be no similar equivalent in the Microsoft.SQlServer.management.IntegreationServices.  Any ideas how I could set connection strings using the new Managed Object model?  I am executing packages that live in the Catalog.

  • Hi very good.

    Could you please let me know how to create connection manager in project level on SSIS2008.

Page 1 of 1 (5 items)