Create your own custom connection manager

Case
I want to create my own custom SSIS Connection Manager with a GUI. How do you do that?

Solution
A custom connection manager is only for storing properties from a connection. You also need to create custom task or transformation to use those connection manager properties. For this example I will create a very basic connection manager which you can extend for your own needs. It stores a URL, a password and an username. The Connection Manager is for SSIS 2008 and 2012 and I will use Visual Studio 2010 to create it. Programming language is C#. Use this page to translate the code to VB.Net if you prefer that language.

My first SSIS Connection Manager




1) Create Visual Studio project
For my connection manager I used two C# Class Library projects. One for the GUI/editor and one for the code. For SSIS 2008 I will use .Net framework 3.5 and for SSIS 2012 I will use .Net framework 4.0

Two projects for my Connection Manager

2) Create key for strongname
You need to strongname your DLL's so that SSIS can use them. More about that in this Codeguru article: Giving a .NET Assembly a Strong Name. Open the Visual Studio 2010 Command Prompt (in Windows start menu). Browse to your project folder and execute the following command to create a key file: sn.exe -k myConnectionManager.snk


Microsoft (R) .NET Framework Strong Name Utility



3) Add key to project
The key file should be added to both projects.

Add key to projects

And after adding them, you need to sign the projects. Go to the properties of the projects and then to the Signing page. There you can sign the assembly with your newly generated key. Do this for both projects.

Sign Assembly

4) Adding SSIS reference
We need to add references to SSIS libraries. The GUI project needs two references:

• Microsoft.SqlServer.Dts.Design
• Microsoft.SQLServer.ManagedDTS

And the code project only needs one reference:

• Microsoft.SQLServer.ManagedDTS

For SSIS 2008 they can be found in the program files folder. Something like:
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
And for SSIS 2012 they are located in the GAC. Something like:
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ManagedDTS.dll

Add references

5) Build Events
To use the connection managers dll's in SSIS you need to copy them to the GAC and to the connections folder of SSIS. With the Build Events you can do that automatically when you build the visual studio project. Go to the properties of your projects and then to the Build Events. Add the following command to the Post-Build events.

2008
cd $(ProjectDir)
@SET CONNECTIONSDIR="C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Connections\"
@SET CONNECTIONSDIR64="C:\Program Files\Microsoft SQL Server\100\DTS\Connections\"
@SET GACUTIL="C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\gacutil.exe"
Echo Installing dll in GAC
Echo $(OutDir)
Echo $(TargetFileName)
%GACUTIL% -if "$(OutDir)$(TargetFileName)"
Echo Copying files to Connections 32bit
copy "$(OutDir)$(TargetFileName)" %CONNECTIONSDIR%
Echo Copying files to Connections 64bit
copy "$(OutDir)$(TargetFileName)" %CONNECTIONSDIR64%

2012
cd $(ProjectDir)
@SET CONNECTIONSDIR="C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Connections\"
@SET CONNECTIONSDIR64="C:\Program Files\Microsoft SQL Server\110\DTS\Connections\"
@SET GACUTIL="C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe"
Echo Installing dll in GAC
Echo $(OutDir)
Echo $(TargetFileName)
%GACUTIL% -if "$(OutDir)$(TargetFileName)"
Echo Copying files to Connections 32bit
copy "$(OutDir)$(TargetFileName)" %CONNECTIONSDIR%
Echo Copying files to Connections 64bit
copy "$(OutDir)$(TargetFileName)" %CONNECTIONSDIR64%

Post-build events

Make sure you run Visual Studio as Administrator. Otherwise the build events won't work.

6) Icons
I have added the same icon file to both projects. In the properties of the icon file you have to set Build Action to "Embedded Resource". In the UI project you can use the icon in your Windows Form. This will show when you edit the connection manager.

Icon for editor

The other project has an attribute called IconResource which gives Tasks and Transformations there custom icon (see step 9). It should be filled with the assembly name and the icon name. In my project it's "SSISJoost.myConnectionManager" + ".myConnectionManager.ico". Although this is a valid property according to msdn, it doesn't seem to work for Connection Managers. I will post an update if there is a solution or workaround available.


7) Gui project code
To keep everything clear and easy to explain I created a simple connection manager. In the GUI you can add a URL, UserName and password. The password will be a sensitive field so that it wont show in the source code. See the code comments for the explanation.

myConnectionManagerInterface.cs

// C# code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Design;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Design;

// Interface for connection editor
namespace SSISJoost
{
    public class myConnectionManagerInterface : IDtsConnectionManagerUI
    {
        private ConnectionManager _connectionManager;
        private IServiceProvider _serviceProvider;

        public myConnectionManagerInterface()
        {
        
        }

        public void Initialize(ConnectionManager connectionManager, IServiceProvider serviceProvider)
        {
            this._connectionManager = connectionManager;
            this._serviceProvider = serviceProvider;
        }


        public ContainerControl GetView()
        {
            myConnectionManagerEditor editor = new myConnectionManagerEditor();
            editor.ConnectionManager = this._connectionManager;
            editor.ServiceProvider = this._serviceProvider;
            return editor;
        }

        public void Delete(IWin32Window parentWindow)
        {
        }

        public void New(IWin32Window parentWindow)
        {
        }

        public bool Edit(System.Windows.Forms.IWin32Window parentWindow, Microsoft.SqlServer.Dts.Runtime.Connections connections, Microsoft.SqlServer.Dts.Runtime.Design.ConnectionManagerUIArgs connectionUIArg)
        {
            myConnectionManagerEditor editor = new myConnectionManagerEditor();

            editor.Initialize(_connectionManager, this._serviceProvider);
            if (editor.ShowDialog(parentWindow) == DialogResult.OK)
            {
                editor.Dispose();
                return true;
            }
            else
            {
                editor.Dispose();
                return false;
            }
        }

        public bool New(System.Windows.Forms.IWin32Window parentWindow, Microsoft.SqlServer.Dts.Runtime.Connections connections, Microsoft.SqlServer.Dts.Runtime.Design.ConnectionManagerUIArgs connectionUIArg)
        {
            myConnectionManagerEditor editor = new myConnectionManagerEditor();
            
            editor.Initialize(_connectionManager, this._serviceProvider);
            if (editor.ShowDialog(parentWindow) == DialogResult.OK)
            {
                editor.Dispose();
                return true;
            }
            else
            {
                editor.Dispose();
                return false;
            }
        }
    }
}

myConnectionManagerEditor.cs (code behind from windows form)

// C# code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Design;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Design;


namespace SSISJoost
{
    public partial class myConnectionManagerEditor : Form
    {
        #region General Connection Manager Methods
        // Setting and getting ConnectionManager
        private ConnectionManager _connectionManager;
        public ConnectionManager ConnectionManager
        {
            get { return _connectionManager; }
            set { _connectionManager = value; }
        }

        // Setting and getting ServiceProvider
        private IServiceProvider _serviceProvider = null;
        public IServiceProvider ServiceProvider
        {
            get { return _serviceProvider; }
            set { _serviceProvider = value; }
        }

        // Default constructor
        public myConnectionManagerEditor()
        {
            InitializeComponent();
        }
        
        public void Initialize(ConnectionManager connectionManager, IServiceProvider serviceProvider)
        {
            this._connectionManager = connectionManager;
            this._serviceProvider = serviceProvider;
        }
        #endregion

        #region Page Load
        // Fill the fields of the form. Get data from connectionManager object
        private void SMTP2Editor_Load(object sender, EventArgs e)
        {
            this.txtName.Text = this._connectionManager.Name;
            this.txtDescription.Text = this._connectionManager.Description;
            this.txtURL.Text = this._connectionManager.Properties["URL"].GetValue(_connectionManager).ToString();
            this.txtUserName.Text = this._connectionManager.Properties["UserName"].GetValue(_connectionManager).ToString();
            this.txtPassword.Text = this._connectionManager.Properties["Password"].GetValue(_connectionManager).ToString();
        }
        #endregion

        #region Buttons
        // Save value from fields in connectionManager object
        private void btnOK_Click(object sender, EventArgs e)
        {
            this._connectionManager.Name = this.txtName.Text;
            this._connectionManager.Description = this.txtDescription.Text;
            this._connectionManager.Properties["URL"].SetValue(this._connectionManager, this.txtURL.Text);
            this._connectionManager.Properties["UserName"].SetValue(this._connectionManager, this.txtUserName.Text);
            this._connectionManager.Properties["Password"].SetValue(this._connectionManager, this.txtPassword.Text);
            this.DialogResult = DialogResult.OK;
        }

        // Cancel diolog
        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.Cancel;
        }

        // Show some helpful information
        private void btnHelp_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Help");
        }
        #endregion
    }
}

8) Get PublicKeyToken
For the other project you need the PublicKeyToken of the GUI assembly. So first build the GUI project and then, via the same command prompt of step 2, execute the following command in the BIN folder of your GUI project: sn.exe -T SSISJoost.myConnectionManagerUI.dll

Copy the number generated. You need it in the next project.



9) The code for the actual work
This is the code from the project that does the actual storing of the properties in the package XML. See the comments for the explanation.

// C# code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;

namespace SSISJoost
{
    // Connection to the editor assembly. Copy the PublicKeyToken from the previous step.
    [DtsConnection(
        ConnectionType = "myConnectionManager",
        DisplayName = "My Connection Manger",
        ConnectionContact = "SSISJoost",
        Description = "My Custom Connection manager",
        //IconResource = "SSISJoost.myConnectionManager.myConnectionManager.ico", // Bug in SSIS => No custom icons for Connection Managers
        UITypeName = "SSISJoost.myConnectionManagerInterface,SSISJoost.myConnectionManagerUI,Version=1.0.0.0,Culture=neutral,PublicKeyToken=80664248b6de6485")
    ]
    public class myConnectionManager : ConnectionManagerBase, IDTSComponentPersist
    {
        #region Variables for internal use
        // The template for the connectionstring, but without the sensitive password property
        private const string CONNECTIONSTRING_TEMPLATE = "URL=<URL>;UserName=<UserName>;";
        #endregion

        #region Get Set Properties
        /*
         * The properties of my connection manager that
         * will be saved in the XML of the SSIS package.
         * You can add a Category and Description
         * for each property making it clearer.
         */
        private string _connectionString = String.Empty;
        public override string ConnectionString
        {
            get
            {
                UpdateConnectionString();
                return _connectionString;
            }
            //connectionstring is now readonly
            //set
            //{
            //    _connectionString = value;
            //}
        }

        private string _url = String.Empty;
        [CategoryAttribute("my connection manager")]
        [Description("Some URL to do something with in an other task or transformation")]
        public string URL
        {
            get { return this._url; }
            set { this._url = value; }
        }

        private string _userName = String.Empty;
        [CategoryAttribute("my connection manager")]
        [Description("The username needed to access the url")]
        public string UserName
        {
            get { return this._userName; }
            set { this._userName = value; }
        }

        // Notice the password property to hide the chars
        private string _password = String.Empty;
        [CategoryAttribute("my connection manager")]
        [Description("The secret password")]
        [PasswordPropertyText(true)]
        public string Password
        {
            get { return this._password; }
            set { this._password = value; }
        }
        #endregion

        #region Overriden methods
        public override object AcquireConnection(object txn)
        {
            // Set the connectionstring
            UpdateConnectionString();
            return base.AcquireConnection(txn);
        }

        public override void ReleaseConnection(object connection)
        {
            base.ReleaseConnection(connection);
        }

        public override DTSExecResult Validate(IDTSInfoEvents infoEvents)
        {
            // Very basic validation example:
            // Check if the URL field is filled.
            // Note: this is a runtime validation
            // In the form you can add some more
            // designtime validation.
            if (string.IsNullOrEmpty(_url))
            {
                infoEvents.FireError(0, "My Custom Connection Manager", "URL is mandatory.", string.Empty, 0);
                return DTSExecResult.Failure;
            }
            else
            {
                return DTSExecResult.Success;
            }
        }
        #endregion

        #region Update ConnectionString
        private void UpdateConnectionString()
        {
            // Create a connectionstring, but without sensitive properties like the password
            String connectionString = CONNECTIONSTRING_TEMPLATE;

            connectionString = connectionString.Replace("<URL>", URL);
            connectionString = connectionString.Replace("<UserName>", UserName);

            _connectionString = connectionString;
        }
        #endregion

        #region Methods for IDTSComponentPersist
        // These two methods are for saving the data in the package XML without showing sensitive data
        void IDTSComponentPersist.LoadFromXML(System.Xml.XmlElement node, IDTSInfoEvents infoEvents)
        {
            // Checking if XML is correct. This might occur if the connection manager XML has been modified outside BIDS/SSDT
            if (node.Name != "MYCONNECTIONMANAGER")
            {
                throw new Exception(string.Format("Unexpected connectionmanager element when loading task - {0}.", "MYCONNECTIONMANAGER"));
            }
            else
            {
                // Fill properties with values from package XML
                this._userName = node.Attributes.GetNamedItem("UserName").Value;
                this._url = node.Attributes.GetNamedItem("URL").Value;


                foreach (XmlNode childNode in node.ChildNodes)
                {
                    if (childNode.Name == "Password")
                    {
                        this._password = childNode.InnerText;
                    }
                }
                this._connectionString = node.Attributes.GetNamedItem("ConnectionString").Value;
            }
        }

        void IDTSComponentPersist.SaveToXML(System.Xml.XmlDocument doc, IDTSInfoEvents infoEvents)
        {
            XmlElement rootElement = doc.CreateElement("MYCONNECTIONMANAGER");
            doc.AppendChild(rootElement);

            XmlAttribute connectionStringAttr = doc.CreateAttribute("ConnectionString");
            connectionStringAttr.Value = _connectionString;
            rootElement.Attributes.Append(connectionStringAttr);

            XmlAttribute userNameStringAttr = doc.CreateAttribute("UserName");
            userNameStringAttr.Value = _userName;
            rootElement.Attributes.Append(userNameStringAttr);

            XmlAttribute urlStringAttr = doc.CreateAttribute("URL");
            urlStringAttr.Value = _url;
            rootElement.Attributes.Append(urlStringAttr);

            if (!string.IsNullOrEmpty(_password))
            {
                XmlElement passwordElement = doc.CreateElement("Password");
                rootElement.AppendChild(passwordElement);
                passwordElement.InnerText = _password;

                // This will make the password property sensitive
                XmlAttribute passwordAttr = doc.CreateAttribute("Sensitive");
                passwordAttr.Value = "1";
                passwordElement.Attributes.Append(passwordAttr);
            }
        }
        #endregion
    }
}

10) The Result
After building / deploying the solution, you need to close/reopen BIDS/SSDT because the GAC is cached on startup. Now you can use your new connection manager. But without any custom tasks or transformations it, it's basically useless... You could use a Script Task to get the connectionstring or a certain property.


11) Download
You can download the complete Visual Studio 2010 example solutions:
SSIS 2008 version
SSIS 2012 version

Note: this is a very basic example. Use it as a base and check other examples like the one below.
More info: MSDN, KenR, Matt Masson (twice)