Using DtsMappingSurfaceControl in your Custom SSIS Component

As one of the "wishes" I'd had when creating my custom Kimball Method SCD component for SSIS, I'd wanted to give myself the same kind of column mapping interface that Microsoft had given other components - like the Merge Join or the OLE DB Destination components.  It's a relatively elegant interface (even if somewhat prone to drag and drop misses) providing a decent view of the columns involved, and an easy way to link them up.

I had posted a question in the forums about how to go about doing that, and hadn't gotten much response until KimYda did a little more digging than I had, and came up with a gem.  The control was "hidden" away in a Microsoft assembly they used internally.  Kim didn't provide any other details, so I had to plug away at it a bit until I felt satisfied that I could reasonably use the control.  Well, I can only hope I'm doing some extra legwork because I don't have access to internal Microsoft documentation and/or object model - because if not, I truly pity the developers on the SSIS team that have to wire up this control manually.  It's way too involved, and I wrote a wrapper even though I only use it in two places!  (You'll find the link below.)

The Short Version

You'll need to add six assembly references, add the mapping control to the toolbox, add the control to your design surface, and add a tooltip control to your UI.  In the code, you'll have to add drag and drop events and handling code, and should add handling for tooltips for the all important metadata.  Last but not least is translating "mappings" to and from the control so you can persist them in your component.

If you're looking for a shortcut, you can download two C# source files from my SkyDrive.  I've extracted the wrapper from my Kimball Method SCD project, dumping some XmlSerialization code out.  The SkyDrive version ought to work, but the "latest" bugfixed/new featured version will always be in the Kimball Method SCD project source - it's in there in the "SSIS Custom Component Utilities" project, in "DtsMappingSurfaceControlWrapper.cs" and "ColumnInformation.cs" source files.  (At least, it will be once I release v1.5...)  It's quite involved (read on to see why), and you'll have to remove some code if you don't want to use my property persistence framework.

The Details

Reference The Assemblies

KimYda noted that the "Microsoft.DataWarehouse" assembly contained the controls I was looking for.  Over the course of actually making this thing work, I found five other assemblies which are essential.  The six assemblies you'll need to add as references are:

• Microsoft.DataTransformationServices.Controls* (in the GAC)
• Microsoft.DataTransformationServices.Design (in \Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\)
• Microsoft.DataWarehouse (in \Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\)
• Microsoft.DataWarehouse.Interfaces (in \Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\)
• Microsoft.SqlServer.DlgGrid* (in the GAC)
• Microsoft.SqlServer.GridControl  (in \Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2\)

Four of those (non-starred) you should be able to find via the standard "add references" method using the .Net or Browse tabs.  Two of them you'll have to go to extra lengths with.  Keep in mind that I'm running Windows 7 x64 - that's why there's references in the paths above to Program Files (x86).  If you're running a 32-bit Windows, just refer to Program Files, or use your favourite file search facility.  If you can't find those DLLs where I did, first look in the GAC, then do a search.  You may have to do the following workaround with some of the assemblies (I did on a SQL 2005 x86 setup).

Microsoft.DataTransformationServices.Controls is in the GAC... but you can't see it (at least I couldn't) using Windows Explorer or Windows 7's integrated file search.  You'll have to fire up an (Administrator-privileged) command line, manually navigate to C:\Windows\Assembly, and down into GAC_MSIL.  There, you'll see a folder with the name you're looking for - now you just have to (painfully) drill down in there until you uncover the DLL.  Now you'll have to copy the DLL out of that folder into another location and reference it from there - because even though you've located it, the Add Reference browse won't see subfolders in the Assembly folder like the command line will.  I copied it right out to my source folder so I wouldn't have to look very far in the future.

The same song and dance occurs with Microsoft.SqlServer.DlgGrid - it's in the GAC, but inaccessible to the Add Reference wizard.  The oddity here is that it's in the "90" subfolder, even though I don't have SQL 2005 installed.  Well, I'm sure there's a reason - but I have no clue why it's there.

Add the DtsMappingSurfaceControl To Toolbox

Open up your VStudio toolbox, right-click and "Choose Items..."  Scroll around and find DtsMappingSurfaceControl and check the box to the left.  You'll get an inspired looking gear icon in your toolbox.  (They never intended these tools for public consumption, so why waste time on icons?)

Add a DtsMappingSurfaceControl To Your UI

Drag that new control onto your design surface.  It doesn't look like much - but you should see the a scrollable pane with a "subwindow" in it titled "Available Input Columns".  Excellent - we're almost 10% of the way there... ;)

Add a Tooltip Control

I recommend you add a tooltip control to your form to supply the metadata info (column data type, length) to the user when they float the mouse over them.  It's optional, but remember how irritating the lack of that feature is on the Derived Column component!

Initialize the DtsMappingSurfaceControl

Time to get into code!  From here on, there will be a lot of use of the words "first" and "second" when referring to the column lists and parts of the surface mapping control.  The "first" table/control/grid refers to the one that shows up to the left of the surface mapping control, and the "second" table/control/grid refers to the one that shows up on the right hand side of the control.  Well, initially anyway - the user can move them around wherever they like - that's probably why the SSIS team refers to them as "first" and "second". 

When I'm exposing what code to use, I'll be mentioning "FirstMovableGridControl" and "SecondMovableGridControl" a lot.  To keep things short, when possible, I'll refer to "XMovableGridControl" when what I'm talking to applies to either of those properties/objects.

Step one, set up some properties on the control.  For my purposes, I wanted to show two tables to link columns between them, so I set:

• TwoTables property to true
• XMovableGridControl.GridLayout to Check_Name
• XMovableGridControl.Text to the first and second table names, respectively.

You'll have to supply the control with the column names for the first and second tables by constructing an array of ColumnInfo structures, and passing those arrays to InitializeFirstTable and InitializeSecondTable.  It's unfortunate that the ColumnInfo structure isn't more comprehensive and contain data type information - if it did, then the control could handle tooltip events itself.
Add Drag/Drop Events and Handlers
Next, you'll have to set up the drag and drop event ecosystem.  This was the difficult part (for me) as I'd never used drag and drop extensively, AND the sub-controls involved in this composite control were not consistent.  I'll try to highlight the inconsistency as we go. 
In broad strokes, you'll need to enable dragging and dropping, add a MouseDown, MouseUp, and MouseMove for each "side" to handle the "start" of the drag, and add a DragOver and DragDrop for each side to handle the "drop" part of the drag.  Almost all of those event handlers get placed on the GridControl of the XMovableGridControl, like this:
dtsMappingSurfaceControl.FirstMovableGridControl.GridControl.MouseDown += new MouseEventHandler(YourEventHandlerHere)
I say "almost all" because the DragOver and DragDrop event handlers for the FirstMovableGridControl are tied directly to that control, not to the GridControl property of that control.  To restate, add event handlers for all the above mentioned events to the XMovableGridControl.GridControl objects except for FirstMovableGridControl's DragOver and DragDrop.  Add those event handlers directly to FirstMovableGridControl, not to its GridControl property.
Now, in order to determine if the user is dragging instead of just moving the mouse around, we'll need to keep track of where the user was when they initially clicked the mouse.  So in the MouseDown event handler, store the mouse location (supplied in the MouseEventArgs) into a class-level variable.  The MouseUp event handler should clear that variable (by setting it to Point.Empty).
The (first) complicated part occurs in the MouseMove event handler.  Here, you'll have to first determine if the mouse has moved sufficiently to initiate a drag/drop operation.  You'd do that by comparing the location you stored in the MouseDown event to the location passed in the MouseEventArgs of the MouseMove, and seeing if that wanders outside the box prescribed by SystemInformation.DragSize.
If you have moved enough with the mouse button down to start dragging, you need to know what you're dragging.  Call the XMovableGridControl.GridControl.HitTest() method to get back a HitTestInfo structure.  One of the properties on that structure is the RowIndex.  If that property is zero or greater, the drag started on a valid column (row? :) ).  If it's a negative value, the user tried to drag the column header or other part of the control.  Use the RowIndex to retrieve the column name by using the XMovableGridControl.GridControl.GetCellString method, passing the RowIndex as well as the column index for the column name.  Use XMovableGridControl.ColumnNameIndex to supply that index.  Doing all that should have retrieved the column name that the user's started to drag.  Next you need to call XMovableGridControl.DoDragDrop() to start the drag operation.
Great!  You've got the drag started, so it's time to code the event(s) to handle the drop.  The DragOver event changes the icon to the appropriate "can't drop here" or "drop here" icon.  I've chosen to set the DragEventArgs.Effect to DragDropEffects.None when being dragged over anything but the destination control.  (Watch out here for the inconsistency between First and Second.)
The important part - and second complex part in this drag/drop dance - is the destination control's DragDrop event handler.  First, extract the source column name from the drag/drop operation by calling DragEventArgs.Data.GetData(typeof(System.String)).  Second, we have to find out where the user's decided to drop the link.  Do that by interpreting the drop location using XMovableGridControl.PointToClient.  (We haven't had to interpret locations like this anywhere else before - so don't think I missed this step elsewhere.)  Then perform the same HitTest method call as you did in the MouseMove event handler - except on the receiving XMovableGridControl.  The HitTestInfo structure returned will tell you your destination column name.
Now that you have the source column name and the destination, you can instruct the control to draw the "mapping" line between those two items.  You can do that by using the DtsMappingSurfaceControl's AddConnector method.  There are two things to note with this:

1. You need to supply the arguments to the AddConnector method in the "first", "second" order of the tables, not the "source", "destination" order of the drag/drop operation.  To restate, if the user dragged a column from the "right" to the "left", you would have to reverse the arguments to the AddConnector method.
2. If the second table's column is involved in any other connection, the AddConnection call will (silently) fail.  You need to call the DeleteConnectors method first - but to do that, you need to know what "first" column the second column is connected to, which means you need to find it in the Connections collection.

Whew.  That was a moutful - and a handful of work for you to do.
Add Tooltip Handlers
I've set up a tooltip object in my wrapper class.  If you choose to implement tooltips for your control, there are a few issues you'll have to deal with:

• You'll need to dynamically set the tooltip message in your MouseMove event.
• You'll have to trigger the tooltip to display "manually", because it only displays itself automatically when you move from control to control... which doesn't include moving from row to row (column to column?) within the surface mapping control.
• You'll have to supply the metadata information, because the surface mapping control doesn't have it.

Add Mapping Code
At this point, you should have something you can "play" with at design-time in SSIS - but you don't have any way to set the connections before you show the control, nor retrieve the connections the user has set.
To set up the connections before the control is shown, call the control's AddConnector method, passing it column names.
To retrieve the columns involved in connections after the user has used the control, iterate over the DtsSurfaceConnection objects in the Connections collection of the control.  You're interested in the SourceConnectorObject and DestinationConnectorObject.  The "source" and "destination" here do not refer to any directionality in the mapping (to my knowledge) - the "source" refers to the "first" table, and the "destination" refers to the "second".
I'm Done!
Wow - you read all of that and built your own "mapping" interface?  Congratulations are in order - or maybe we can commiserate with each other on how much punishment we're willing to inflict on ourselves in order to get this far.  You could have always taken the short-cut of downloading the source from my SkyDrive or CodePlex... I wish I could have!
Using this, I was able to turn two tabs in my component editor that looked like this:

 To something I think looks (and operates) a lot nicer:

Have fun with your component!