SSIS provides a mechanism to automatically encrypt the sensitive properties of your Connection Manager (or Task) based on the package ProtectionLevel. To make use of this in your custom code, you’ll need to do two things:
- Implement the IDTSComponentPersist interface
- Add a Sensitive=”1” attribute to one or more elements of your persisted Connection Manager (or Task)
Sample code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
public void SaveToXML(XmlDocument doc, IDTSInfoEvents events) { // -- 1 -- Create an root node for the data var rootElement = doc.CreateElement("MyConnectionManagerRootElement"); doc.AppendChild(rootElement); // -- 2 -- Persist the connection string excluding the password // alternatively, we could persist as a child element var attr = doc.CreateAttribute("ConnectionString"); attr.Value = this.ConnectionString; // should return CS without password rootElement.Attributes.Append(attr); // -- 3 -- Persist the password // We check for null/empty because we don't need to encrypt an empty value if (!string.IsNullOrEmpty(m_password)) { // This must always be its own child element, since it needs an attribute var passwordElement = doc.CreateElement("Password"); rootElement.AppendChild(passwordElement); // This is the magic part // By adding the Sensitive="1" attribute, the SSIS runtime // knows to automatically encrypt this value when persisting the // package XML passwordElement.InnerText = m_password; attr = doc.CreateAttribute("Sensitive"); attr.Value = "1"; passwordElement.Attributes.Append(attr); } } public void LoadFromXML(XmlElement rootNode, IDTSInfoEvents events) { string strConnString = ""; // -- 1 -- Create an root node for the data if (rootNode.Name != "MyConnectionManagerRootElement") { throw new Exception("This is not my beautiful node!"); } // -- 2 -- Unpersist the ConnectionString XmlNode attr = rootNode.Attributes.GetNamedItem("ConnectionString"); if (attr != null) { strConnString = attr.Value; } // -- 3 -- Unpersist the password // Note, we do not need to decrypt anything here. The SSIS runtime // will have already decrypted it for us. foreach (XmlNode childNode in rootNode.ChildNodes) { if (childNode.Name == "Password") { strConnString += "Password=" + childNode.InnerText + ";"; } } // Set the connection string this.ConnectionString = strConnString; } |