private void ScanPrecedenceConstraints(string containerID, PrecedenceConstraints constraints, TreeNode parent)
{
if (this.CancellationPending)
{
return;
}
TreeNode constraintsNode = AddFolder("PrecedenceConstraints", parent);
foreach (PrecedenceConstraint constraint in constraints)
{
// Check expressions
if (constraint.EvalOp == DTSPrecedenceEvalOp.Constraint)
{
// Continue, when no expression used
continue;
}
if (string.IsNullOrEmpty(constraint.Expression))
{
// Continue, when expression is empty
continue;
}
string match;
if (ExpressionMatch(constraint.Expression, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(constraintsNode, "Expression", GetImageIndex(IconKeyPrecedenceConstraint), constraint, true);
}
}
}
private void ScanVariables(Variables variables, TreeNode parent, string currentPath)
{
if (this.CancellationPending)
{
return;
}
TreeNode variablesFolder = AddFolder("Variables", parent);
int imageIndex = GetImageIndex(IconKeyVariableExpression);
foreach (Variable variable in variables)
{
if (!variable.EvaluateAsExpression)
{
continue;
}
// Check path to ensure variable is parented by current scope
// only, not by child containers that inherit the variable
if (!variable.GetPackagePath().StartsWith(currentPath + ".Variables["))
{
continue;
}
string match;
if (ExpressionMatch(variable.Expression, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(variablesFolder, variable.QualifiedName, imageIndex, variable, true);
}
}
}
private void PropertyMatch(TreeNode parent, object property, string propertyName, string value)
{
IEnumerable valueList;
// If the property value contains a delimited list, we need to split it, e.g Script Task
if (propertyName == "ReadOnlyVariables" || propertyName == "ReadWriteVariables")
{
// Comma delimited list of variable names, split and then search below
valueList = value.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
}
else
{
valueList = new string[] { value };
}
foreach (string item in valueList)
{
string match;
if (PropertyMatchEval(item, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(parent, propertyName, GetImageIndex(IconKeyProperty), property, true);
}
}
}
private void CheckExecuteSQLTask(TaskHost taskHost, TreeNode parent)
{
ExecuteSQLTask task = taskHost.InnerObject as ExecuteSQLTask;
TreeNode parameterBindings = AddFolder("ParameterBindings", parent);
foreach (IDTSParameterBinding binding in task.ParameterBindings)
{
string match;
string value = binding.DtsVariableName;
if (!string.IsNullOrEmpty(value) && PropertyMatchEval(value, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(parameterBindings, binding.ParameterName.ToString(), GetImageIndex(IconKeyProperty), binding, true);
}
}
TreeNode resultSetBindings = AddFolder("ResultSetBindings", parent);
foreach (IDTSResultBinding binding in task.ResultSetBindings)
{
string match;
string value = binding.DtsVariableName;
if (!string.IsNullOrEmpty(value) && PropertyMatchEval(value, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(resultSetBindings, binding.ResultName.ToString(), GetImageIndex(IconKeyProperty), binding, true);
}
}
}
private void CheckForEachLoop(ForEachLoop forEachLoop, TreeNode parent)
{
// Check properties of loop itself
ScanProperties(forEachLoop, parent);
// Check properties of enumerator, when present
ForEachEnumeratorHost enumerator = forEachLoop.ForEachEnumerator;
if (enumerator != null)
{
TreeNode enumeratorFolder = AddFolder(enumerator.GetType().Name, parent);
ScanProperties(enumerator, enumeratorFolder);
}
// Check the (output) variable mappings
TreeNode variableMappings = AddFolder("VariableMappings", parent);
foreach (ForEachVariableMapping mapping in forEachLoop.VariableMappings)
{
string match;
string value = mapping.VariableName;
if (!string.IsNullOrEmpty(value) && PropertyMatchEval(value, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(variableMappings, mapping.ValueIndex.ToString(), GetImageIndex(IconKeyProperty), mapping, true);
}
}
}
private void ScanProperties(IDTSPropertiesProvider provider, TreeNode parent)
{
if (this.CancellationPending)
{
return;
}
TreeNode properties = AddFolder("Properties", parent);
TreeNode expressions = AddFolder("PropertyExpressions", parent);
// New 2012 + interface implemented by Package, Sequence, DtsEventHandler, ForLoop, ForEachLoop
// There are other objects that implement IDTSPropertiesProvider, and therefore support expressions, e.g. ConnectionManager, Variable
// However we can use it to skip objects that have no expressions set, by using HasExpressions property
bool hasExpressions = true;
IDTSPropertiesProviderEx providerEx = provider as IDTSPropertiesProviderEx;
if (providerEx != null)
{
hasExpressions = providerEx.HasExpressions;
}
foreach (DtsProperty property in provider.Properties)
{
TypeCode propertyType = property.Type;
string propertyName = property.Name;
#region Check property value
string match;
if (property.Type == TypeCode.String && property.Get)
{
string value = property.GetValue(provider) as string;
if (!string.IsNullOrEmpty(value))
{
PropertyMatch(properties, new DisplayProperty(property, value), propertyName, value);
}
}
#endregion
#region Check property expression
string expression = provider.GetExpression(property.Name);
if (expression == null)
{
continue;
}
// Check this for a while, before we trust it, simce it is undocumented.
System.Diagnostics.Debug.Assert(hasExpressions, "HasExpressions was false, but we have an expression.");
if (ExpressionMatch(expression, out match))
{
VariableFoundEventArgs foundArgument = new VariableFoundEventArgs();
foundArgument.Match = match;
OnRaiseVariableFound(foundArgument);
AddNode(expressions, propertyName, GetImageIndex(IconKeyVariableExpression), new PropertyExpression(propertyName, expression, PackageHelper.GetTypeFromTypeCode(property.Type)), true);
}
#endregion
}
}
private void ExpressionMatchConstraint(TreeNode constraintsNode, PrecedenceConstraint constraint)
{
foreach (string match in expressionCandidateMatches.Where(matchCandidate => constraint.Expression.Contains(matchCandidate)).ToList())
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(constraintsNode, "Expression", GetImageIndex(IconKeyPrecedenceConstraint), constraint, true);
}
}
private void ExpressionMatchProperty(TreeNode expressions, DtsProperty property, string propertyName, string expression)
{
foreach (string match in expressionCandidateMatches.Where(matchCandidate => expression.Contains(matchCandidate)).ToList())
{
VariableFoundEventArgs foundArgument = new VariableFoundEventArgs();
foundArgument.Match = match;
OnRaiseVariableFound(foundArgument);
AddNode(expressions, propertyName, GetImageIndex(IconKeyVariableExpression), new PropertyExpression(propertyName, expression, PackageHelper.GetTypeFromTypeCode(property.Type)), true);
}
}
private void ExpressionMatchVariable(TreeNode variablesFolder, int imageIndex, Variable variable)
{
foreach (string match in expressionCandidateMatches.Where(matchCandidate => variable.Expression.Contains(matchCandidate)).ToList())
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(variablesFolder, variable.QualifiedName, imageIndex, variable, true);
}
}
private void ScanCustomPropertiesCollection(IDTSCustomPropertyCollection100 properties, TreeNode parent)
{
// First check if we have a "FriendlyExpression". We use the value from FriendlyExpression, because it is CPET_NOTIFY
// The related Expression will always be CPET_NONE, and less readable.
bool friendlyExpressionValid = GetIsFriendlyExpression(properties);
foreach (IDTSCustomProperty100 property in properties)
{
string propertyName = property.Name;
string value = property.Value as string;
if (string.IsNullOrEmpty(value))
{
continue;
}
string match;
if (property.ExpressionType == DTSCustomPropertyExpressionType.CPET_NOTIFY)
{
// Check the expression string for our matching variable name
// We ignore the Task level properties derived from these expressions, because here we have much more context.
// Could have expression properties (CPET_NOTIFY) entirely, call it Darren's OCD in action.
if (ExpressionMatch(value, out match))
{
// For the "FriendlyExpression" property, rename to be Expression
if (friendlyExpressionValid && property.Name == "FriendlyExpression")
{
propertyName = "Expression";
}
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(parent, propertyName, GetImageIndex(IconKeyPropertyExpression), new PropertyExpression(propertyName, value, property.Value.GetType()), true);
}
}
else
{
if (property.Name == "Expression" && friendlyExpressionValid)
{
continue;
}
if (PropertyMatch(propertyName, value, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(parent, propertyName, GetImageIndex(IconKeyProperty), property, true);
}
}
}
}
protected virtual void OnRaiseVariableFound(VariableFoundEventArgs e)
{
// Make a temporary copy of the event to avoid possibility of
// a race condition if the last subscriber unsubscribes
// immediately after the null check and before the event is raised.
EventHandler <VariableFoundEventArgs> handler = VariableFound;
// Event will be null if there are no subscribers
if (handler != null)
{
handler(this, e);
}
}
private void PropertyAsExpressionMatch(DtsProperty property, string expression, TreeNode parent)
{
foreach (string match in expressionCandidateMatches.Where(matchCandidate => expression.Contains(matchCandidate)).ToList())
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
if (parent != null)
{
TreeNode propertiesNode = parent.Nodes["Properties"];
System.Diagnostics.Debug.Assert(!(parent != null && propertiesNode == null), "Properties node doesn't exist when it should already. We will lose this property match. Find the Properties node.");
AddNode(propertiesNode, property.Name, GetImageIndex(IconKeyVariableExpression), new DisplayProperty(property, expression), true);
}
}
}
private void ExpressionMatchCustomProperty(TreeNode parent, bool friendlyExpressionValid, IDTSCustomProperty100 property, string propertyName, string value)
{
foreach (string match in expressionCandidateMatches.Where(matchCandidate => value.Contains(matchCandidate)).ToList())
{
// For the "FriendlyExpression" property, rename to be Expression
if (friendlyExpressionValid && property.Name == "FriendlyExpression")
{
propertyName = "Expression";
}
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(parent, propertyName, GetImageIndex(IconKeyPropertyExpression), new PropertyExpression(propertyName, value, property.Value.GetType()), true);
}
}
private void EnumerateCollection(object task, TreeNode parameterBindings, string propertyName, string valueProperty, string textProperty)
{
IEnumerable listObject = PackageHelper.GetPropertyValue(task, propertyName) as IEnumerable;
foreach (object binding in listObject)
{
string match;
string value = PackageHelper.GetPropertyValue(binding, valueProperty).ToString();
if (!string.IsNullOrEmpty(value) && PropertyMatchEval(value, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(parameterBindings, PackageHelper.GetPropertyValue(binding, textProperty).ToString(), GetImageIndex(IconKeyProperty), binding, true);
}
}
}
private void CheckExecutePackageTask(TaskHost taskHost, TreeNode parent)
{
ExecutePackageTask task = taskHost.InnerObject as ExecutePackageTask;
TreeNode parameterAssignments = AddFolder("ParameterAssignments", parent);
// IDTSParameterAssignment doesn't support foreach enumeration, so use for loop instead.
for (int i = 0; i < task.ParameterAssignments.Count; i++)
{
IDTSParameterAssignment assignment = task.ParameterAssignments[i];
string match;
string value = assignment.BindedVariableOrParameterName;
if (!string.IsNullOrEmpty(value) && PropertyMatchEval(value, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(parameterAssignments, assignment.ParameterName.ToString(), GetImageIndex(IconKeyProperty), assignment, true);
}
}
}
private void CheckExecutePackageTask(TaskHost taskHost, TreeNode parent)
{
// We have a reference to Microsoft.SqlServer.ExecPackageTaskWrap.dll
// Only ever use interfaces which are consistent between versions of SSIS, cannot use reflection because task is native, not managed code.
IDTSExecutePackage100 task = taskHost.InnerObject as IDTSExecutePackage100;
// Potential issue because of multiple version support in SQL Server 2016+. Is the reference to the correct version?
if (task == null)
{
// Task is null following cast to IDTSExecutePackage100, therefore we have the wrong Microsoft.SqlServer.ExecPackageTaskWrap reference
// Produce a false found variable for feedback to the UI, else a null referece exception will break the search
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = "Invalid IDTSExecutePackage100 reference";
OnRaiseVariableFound(info);
AddNode(parent, info.Match, GetImageIndex(IconKeyError), new DisplayProperty("InvalidIDTSExecutePackage100", info.Match), true);
return;
}
TreeNode parameterAssignments = AddFolder("ParameterAssignments", parent);
// IDTSParameterAssignment doesn't support foreach enumeration, so use for loop instead.
// Why? ParameterAssignments -> IDTSParameterAssignments -> IEnumerable
for (int i = 0; i < task.ParameterAssignments.Count; i++)
{
IDTSParameterAssignment assignment = task.ParameterAssignments[i];
string match;
string value = assignment.BindedVariableOrParameterName;
if (!string.IsNullOrEmpty(value) && PropertyMatchEval(value, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(parameterAssignments, assignment.ParameterName.ToString(), GetImageIndex(IconKeyProperty), assignment, true);
}
}
}
private void VariableFound(object sender, VariableFoundEventArgs e)
{
string match = e.Match;
if (match.StartsWith("@"))
{
match = match.Substring(1);
}
if (match.StartsWith("["))
{
match = match.Substring(1, match.Length - 2);
}
// Remove it from our simpel list, for qualified names
if (unusedVariablesList.Remove(match))
{
return;
}
// Try unqualified matches, just check for variables ending in "::VariableName", skipping the namespace
match = "::" + match;
match = unusedVariablesList.Find(v => v.EndsWith(match));
unusedVariablesList.Remove(match);
}
public VariableFoundEventArgs(VariableFoundEventArgs variableFoundEventArgs)
{
this.Match = variableFoundEventArgs.Match;
}
private void VariableFound(object sender, VariableFoundEventArgs e)
{
// Report variable found via BackGroundWorker to ensure we are thread safe when accessing the form control later on
this.processPackage.ReportProgress(0, e);
}
private void processPackage_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
VariableFoundEventArgs variableFound = (VariableFoundEventArgs)e.UserState;
AddValue(variableFound.Type, variableFound.ContainerID, variableFound.ObjectID, variableFound.ObjectType, variableFound.ObjectPath, variableFound.ObjectName, variableFound.PropertyName, variableFound.Value, variableFound.Icon, variableFound.IsExpression);
}
