List all your connection managers

Hello,
I'm building a custom task which has a property ConnectionManager which obviously allows you to select which connection manager you're goinng to use.

I know how to get the list of connection managers but how do I make them appear in a combo-box in the properties pane? Anyone got some code for that? Hopefully this is fairly trivial for you developer types out there.

Thanks
JamieI believe that the key is the TypeConverter property of property objects.
If you were talking about a custom property in the Data Flow (which I realize is not your question) whose values came from an enumeration, using TypeConverter would both constrain the value of the property to an enumeration value, and tell the editor to display available values from the enumeration. From BOL (again, on the Data Flow, but I see that TypeConverter is available on other SSIS property objects):

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C#
IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

TR>

Visual Basic
Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

|||

To get rich behaviour such as list of connections, with <New Connection> option that fires the new conn dialog when selected, you need to go for a UITypeEditor.

Editor(typeof(DtsInputTypeEditor), typeof(System.Drawing.Design.UITypeEditor))
public string MyConnection
{
blah blah
}

You would need to create the DtsInputTypeEditor class, inheriting from UITypeEditor.

public sealed class DtsInputTypeEditor : UITypeEditor, IDisposable
{
blah blah
}

Overriding EditValue of UITypeEditor allows you grab the IWindowsFormsEditorService and throw up your editor control. Mine is a simple control which inherits from ListBox. In the editor control you can then capture the click event and test for <New Conn> and do what you need to do.

You could probably use a type convereter for a simples list and no new functions. Some articles I had gathered to help me.

Getting the Most Out of the .NET Framework PropertyGrid Control
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/usingpropgrid.asp)

Make Your Components Really RAD with Visual Studio .NET Property Browser
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/vsnetpropbrow.asp)

Customized display of collection data in a PropertyGrid - The Code Project - C# Controls
(http://www.codeproject.com/cs/miscctrl/customizingcollectiondata.asp)

|||

If you just want to show list of existing connections, I think coding TypeConverter is much simpler than UITypeEditor. You just need to subclass TypeConverter and implement several functions: GetStandardValuesSupported() should return true, and GetStandardValues() should return list of connection manager names. Property panel will show list returned by GetStandardValues() in drop down box.

UITypeEditor gives you more control and more opportunity to implement custom behavior, but also requires more coding.

|||I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.|||

<BarefacedCheek>
Has anyone got the code that uses TypeConverter to provide a list of your connection managers in a combo box in the properties pane for a custom task? It'd save me an *awful* lot of work in working it out for myself if I could just nab someone else's!!

For example, in

GetStandardValues(...)

in my class that inherits from TypeConverter I need to get a list of connection managers in the task. How on earth do I do that?

Here's what I have already which is quite frankly nothing at all:

public class ConnectionManagerList : TypeConverter
{
public override bool GetStandardValuesSupported(ITypeDescriptorContext context)
{
return true;
}

public override StandardValuesCollection GetStandardValues(ITypeDescriptorContext context)
{
StandardValuesCollection connManList;

//What goes in here?

return connManList;
}
}

Thanks
Jamie
</BarefacedCheek>

|||

Use "context.Instance" to get your class for the property. From that you need to get the Connections collection, which depends on what the class is. Loop the connections and assign to the values collection. Job done. What your class is and how you expose the connections collection is the thinking bit.

|||I'm having some trouble with this. I have a breakpoint in the debugger, and when I view context.Instance, it is a Microsoft.DataTransformationServices.PipelineComponentMetadata class. I can't find any documentation on that class, and it doesn't seem to implement Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90, which is what I would have expected. What is context.Instance supposed to be?|||

The Instance will be whatever is parenting the grid. Looks like this is for a pipeline component, and that will differ from a task for example.

If you want a copy of IDTSComponentMetaData90, then PipelineComponentMetadata appears to have a property DtsComponentMetadata of type IDTSComponentMetaData90.

|||When I try to use the PipelineComponentMetadata object, it is giving me a compiler error, saying that the class is inaccessible :(|||

To get list of all connections managers, use IDtsConnectionService service. It is a public interface in Microsoft.SqlServer.Dts.Design and is documented in Books Online. It can be obtained from appropriate service provider.

PipelineComponentMetadata class is internal implementation details of the designer, don't use it.

|||Thanks Michael. Is there any way to get the output, input, and metadata collections? What I really need for the component I am working on is a list of the External Metadata columns for the first (only) input collection.|||

Where does your code live?

If this is UI Type Editor, you get ITypeDescriptorContext which gives you PropertyDescriptor. You can get the property value from it.

|||

Adam Tybor wrote:

I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.

Returning to this after a few months away...

Good stuff Adam. And that actually works really well for what I want to do.

However, I don't like the displaying of the ID rather than the name so I might decide to go down the UITypeEditor route instead.

-Thanks

Jamie

|||

Adam Tybor wrote:

I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.

Returning to this after a few months away...

Good stuff Adam. And that actually works really well for what I want to do.

However, I don't like the displaying of the ID rather than the name so I might decide to go down the UITypeEditor route instead.

-Thanks

Jamie