List all your connection managers

List all your connection managers

• Nigel Lin

  I 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 < XML:NAMESPACE PREFIX = MSHelp NS = "http://msdn.microsoft.com/mshelp" />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.

  
  

• Crish

  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
  

• Stev92

  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.

  
  
  

• Joe Surfer

  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)
  
  
  
  
  

  
  
  

• jlkyle

  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

  
  
  

• GScully

  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.

  
  
  

• Borgi

  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.
  

• Divyesh Shah

  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

  
  
  

• Spirit of Missouri

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

• RonO

  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.

  
  
  

• jdc_sc

  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.
  

• Brain_Dead_Mind

  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.

  
  
  

• programmer31

  <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>

  
  
  

• AAtallah

  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.
  

  
  
  
• List all your connection managers