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.
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).
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.
Figure 3 - Select connection manager type
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.
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:
Figure 6 - Using project connection managers in SQL Task
The package should successfully run as shown in Figure 7 .
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.
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
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)
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.
Figure 10 - Data flow that populates the cache
Figure 11 - Cache connection manager
Once this is done, the child packages can reference the project connection manager named “Shared CCM” and use them.
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.
Figure 13 - Cache connection manager UI 1
Figure 14 - Cache connection manager UI 2
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.
· 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