List variables on the task UI
This is the most common requirement when designing a SSIS component. Doesn't matter it is a connection manager, a control flow task, a log provider, or a data flow task, you want to give developers some kinds of flexibilities to config the task dynamically.The variables of the SSIS package are stored in the TaskHost class, so to list variables on your task UI, we can use below codes:
foreach (Variable var in TaskHostValue.Variables)
{
if (!var.SystemVariable)
{
cbx.Items.Add(var.QualifiedName);
}
}
It will retrieve all variables stored in the TaskHost class, and if the variable is not a system variable, add it into a combo box (cbx).
Read variable value during the execution phase
Once we done the configuration through our custom UI, certainly we need to get the stored value of the variable during the task execution phase. It actually follows the same way when we do the script task: public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
Variables varFileFormat = null;
variableDispenser.LockOneForRead(FileFormat, ref varFileFormat);
}
In codes listed above, we locked the variable FileFormat stored in VariableDispenser and then read the value from it.
Pass complex configuration setting
Sometimes, actually I think most times, the configuration on the custom UI could be quite complex. To pass the configuration setting to the custom task, you may want to use some easy ways to do it, for example, a data table. Then in the execution phase, we simply need to read row by row to get the setting as a whole.However bear in mind, firstly only simple types can be passed into the task. Here the simple types refer to string type and numeric type.
Now a bit explanation: SSIS package actually will be saved in XML format. So if you store the configuration setting in a complex object type, SSIS won't know how to save it in XML format. Therefore only simple types can be passed into the custom task.
So to allow the setting, for example, a data table, can be saved, at the Task project, we need to implement IDTSComponentPersist interface. Under this interface, we have SaveToXml and LoadFromXML methods, which will tell the package how to save the setting and how to read the setting.
However, to implement IDTSComponentPersist interface, you cannot use "short hand" method to write class properties, for example
//below is not allowed
public string Server { get; set; }
This style, if I am correct, was first introduced in C#3.0. Basically when we write code in this style, the backend compiler handles the property, create a temp object to automate the property implementation , etc. etc. (Well, I am not professional in programming... :( So correct me if I am wrong)
So you might understand now, because backend compiler will create the temp object, the task cannot really identify the relationship between the temp object and the actual property. So to implement the interface, we need to have the getter and setter implemented explictly.
To save your time, I put one of my implementations below.
SaveToXML
It reads a datatable object DtParameters and then create XML tree nodes based on data rows.
//save into package
public void SaveToXML(System.Xml.XmlDocument doc, IDTSInfoEvents infoEvents)
{
XmlElement elementRoot;
XmlNode propertyNode;
elementRoot = doc.CreateElement(string.Empty, "DtParameters", string.Empty);
if (DtParameters != null)
{
if (DtParameters.Rows.Count > 0)
{
//
foreach (DataRow row in DtParameters.Rows)
{
propertyNode = doc.CreateNode(XmlNodeType.Element, "Parameter", string.Empty);
propertyNode.InnerText = row["ParameterValue"].ToString();
XmlAttribute attrParameterName = doc.CreateAttribute("ParameterName");
attrParameterName.Value = row["ParameterName"].ToString();
propertyNode.Attributes.Append(attrParameterName);
XmlAttribute attrRequired = doc.CreateAttribute("Required");
attrRequired.Value = row["Required"].ToString();
propertyNode.Attributes.Append(attrRequired);
elementRoot.AppendChild(propertyNode);
}
}
}
propertyNode = doc.CreateNode(XmlNodeType.Element, "Server", string.Empty);
propertyNode.InnerText = Server;
elementRoot.AppendChild(propertyNode);
propertyNode = doc.CreateNode(XmlNodeType.Element, "SelectedReport", string.Empty);
propertyNode.InnerText = SelectedReport;
elementRoot.AppendChild(propertyNode);
propertyNode = doc.CreateNode(XmlNodeType.Element, "FileName", string.Empty);
propertyNode.InnerText = FileName;
elementRoot.AppendChild(propertyNode);
propertyNode = doc.CreateNode(XmlNodeType.Element, "FileFormat", string.Empty);
propertyNode.InnerText = FileFormat;
elementRoot.AppendChild(propertyNode);
doc.AppendChild(elementRoot);
}
LoadFromXML
It read the package XML node and get the saved parameter values into the datatable _dtparameters
//load from package
public void LoadFromXML(System.Xml.XmlElement node, IDTSInfoEvents infoEvents)
{
_dtparameters = new DataTable();
_dtparameters.Columns.Add("ParameterName", typeof(string));
_dtparameters.Columns.Add("Required", typeof(bool));
_dtparameters.Columns.Add("ParameterValue", typeof(string));
foreach (XmlNode n in node.ChildNodes)
{
if (n.Name == "Server")
{
_server = n.InnerText;
}
if (n.Name == "SelectedReport")
{
_selectedreport = n.InnerText;
}
if (n.Name == "FileName")
{
_filename = n.InnerText;
}
if (n.Name == "FileFormat")
{
_fileformat = n.InnerText;
}
if (n.Name == "Parameter")
{
string parametername = n.Attributes.GetNamedItem("ParameterName").Value;
bool required = Convert.ToBoolean(n.Attributes.GetNamedItem("Required").Value);
string parametervalue = n.InnerText;
_dtparameters.Rows.Add(parametername, required, parametervalue);
}
}
}
So now this topic is done! :D
No comments :
Post a Comment