public Executable AddTask_ExecPackage(string TaskName,
string PackageName,
Sequence sqp = null
)
{
Executable ex = null;
if (sqp == null)
{
ex = p.Executables.Add("Microsoft.ExecutePackageTask");
}
else
{
ex = sqp.Executables.Add("Microsoft.ExecutePackageTask");
}
TaskHost SQLth = (TaskHost)ex;
DtsProperty dt_name = SQLth.Properties["Name"];
DtsProperty dt_pnam = SQLth.Properties["PackageName"];
DtsProperty dt_uref = SQLth.Properties["UseProjectReference"];
dt_name.SetValue(SQLth, TaskName);
dt_pnam.SetValue(SQLth, PackageName);
dt_uref.SetValue(SQLth, true);
return(ex);
}
public DisplayProperty(DtsProperty property, object value)
{
this.Name = property.Name;
this.Value = value;
this.Type = PackageHelper.GetTypeFromTypeCode(property.Type);
this.Get = property.Get;
this.Set = property.Set;
}
private void CheckExpressionTask(TaskHost taskHost, TreeNode parent)
{
// Expression task has the Expression property which we need to treat as an expression rather than a literal value as we do for normal properties.
// Get the Expression value and run an expression match test
DtsProperty property = taskHost.Properties["Expression"];
string expression = property.GetValue(taskHost).ToString();
PropertyAsExpressionMatch(property, expression, parent);
}
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);
}
}
public Executable AddTask_ExecSQL(string taskName,
string connManName,
string statement,
Sequence sqp = null,
SqlStatementSourceType sourceType = SqlStatementSourceType.DirectInput,
ResultSetType resultSetType = ResultSetType.ResultSetType_None)
{
// DirectInput 1
// FileConnection 2
// Variable 3
Executable ex = null;
if (sqp == null)
{
ex = p.Executables.Add("STOCK:SQLTask");
}
else
{
ex = sqp.Executables.Add("STOCK:SQLTask");
}
TaskHost SQLth = (TaskHost)ex;
ExecuteSQLTask est = SQLth.InnerObject as ExecuteSQLTask;
DtsProperty dt_name = SQLth.Properties["Name"];
DtsProperty dt_conn = SQLth.Properties["Connection"];
DtsProperty dt_sttp = SQLth.Properties["SqlStatementSourceType"];
DtsProperty dt_stat = SQLth.Properties["SqlStatementSource"];
dt_name.SetValue(SQLth, taskName);
dt_conn.SetValue(SQLth, connManName);
dt_sttp.SetValue(SQLth, sourceType);
est.ResultSetType = resultSetType;
switch (sourceType)
{
case SqlStatementSourceType.DirectInput:
dt_stat.SetValue(SQLth, statement);
break;
case SqlStatementSourceType.Variable:
dt_stat.SetValue(SQLth, statement);
break;
}
return(ex);
}
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);
}
}
}
public override void Check(Package package, EnvDTE.ProjectItem projectItem)
{
Results.Clear();
string sVisualStudioRelativePath = projectItem.DTE.FullName.Substring(0, projectItem.DTE.FullName.LastIndexOf('\\') + 1);
bool bOfflineMode = false;
try
{
sharedDataWarehouseInterfaces::Microsoft.DataWarehouse.Interfaces.IConfigurationSettings settings = (sharedDataWarehouseInterfaces::Microsoft.DataWarehouse.Interfaces.IConfigurationSettings)((System.IServiceProvider)projectItem.ContainingProject).GetService(typeof(sharedDataWarehouseInterfaces::Microsoft.DataWarehouse.Interfaces.IConfigurationSettings));
bOfflineMode = (bool)settings.GetSetting("OfflineMode");
}
catch { }
List <string> configPaths = new List <string>(package.Configurations.Count);
foreach (Configuration c in package.Configurations)
{
foreach (PackageConfigurationSetting setting in PackageConfigurationLoader.GetPackageConfigurationSettings(c, package, sVisualStudioRelativePath, bOfflineMode))
{
configPaths.Add(setting.Path);
}
}
foreach (ConnectionManager cm in package.Connections)
{
DtsProperty prop = cm.Properties["ConnectionString"];
string sPackagePath = prop.GetPackagePath(cm);
string hasConfig = "does not have ";
bool result = false;
if (configPaths.Contains(sPackagePath) || (!string.IsNullOrEmpty(cm.GetExpression(prop.Name))))
{
hasConfig = "has";
result = true;
}
Results.Add(new Result(result, string.Format("The connection manager {0} {1} a configuration or expression defined for the connection string.", cm.Name,
hasConfig), ResultSeverity.Normal));
}
}
public override void Check(Package package, EnvDTE.ProjectItem projectItem)
{
Results.Clear();
string sVisualStudioRelativePath = projectItem.DTE.FullName.Substring(0, projectItem.DTE.FullName.LastIndexOf('\\') + 1);
bool bOfflineMode = projectItem.ContainingProject.GetOfflineMode();
List <string> configPaths = new List <string>(package.Configurations.Count);
foreach (Microsoft.SqlServer.Dts.Runtime.Configuration c in package.Configurations)
{
foreach (PackageConfigurationSetting setting in PackageConfigurationLoader.GetPackageConfigurationSettings(c, package, sVisualStudioRelativePath, bOfflineMode))
{
configPaths.Add(setting.Path);
}
}
foreach (ConnectionManager cm in package.Connections)
{
DtsProperty prop = cm.Properties["ConnectionString"];
string sPackagePath = prop.GetPackagePath(cm);
string hasConfig = "does not have ";
bool result = false;
if (configPaths.Contains(sPackagePath) || (!string.IsNullOrEmpty(cm.GetExpression(prop.Name))))
{
hasConfig = "has";
result = true;
}
Results.Add(new Result(result, string.Format("The connection manager {0} {1} a configuration or expression defined for the connection string.", cm.Name,
hasConfig), ResultSeverity.Normal));
}
}
/// <summary>
/// Gets the property associated with the specified name.
/// </summary>
/// <param name="properties">The <see cref="DtsProperties"/> collection to check.</param>
/// <param name="name">The name of the <see cref="DtsProperty"/> to find.</param>
/// <param name="property">When this method returns, contains the <see cref="DtsProperty"/> associated with the specified name.
/// The value will be null if the <see cref="DtsProperty"/> was not found. This parameter is passed uninitialized.</param>
/// <returns><c>true</c> if the element was found, otherwise <c>false</c>.</returns>
public static bool TryGetValue(this DtsProperties properties, string name, out DtsProperty property)
{
property = properties.OfType <DtsProperty>().FirstOrDefault(item => item.Name == name);
return(property != null);
}
private object SetPropertyValue(DtsObject dtsObject, string propertyPath, object value)
{
propertyPath = propertyPath.Replace("\\", ".");
object returnValue = null;
string firstPart = propertyPath;
string restOfString = string.Empty;
if (propertyPath.Contains("."))
{
//Can have periods in object names (like connection manager names)
//Need to verify that period is not between an index marker
int delimiterIndex = propertyPath.IndexOf(".");
//while (delimiterIndex > propertyPath.IndexOf("[") &&
// delimiterIndex < propertyPath.IndexOf("]"))
//{
// delimiterIndex = propertyPath.IndexOf(".", delimiterIndex + 1 );
//}
if (delimiterIndex > propertyPath.IndexOf("[") &&
delimiterIndex < propertyPath.IndexOf("]"))
{
delimiterIndex = propertyPath.IndexOf(".", propertyPath.IndexOf("]"));
}
if (delimiterIndex > -1)
{
firstPart = propertyPath.Substring(0, delimiterIndex);
restOfString = propertyPath.Substring(delimiterIndex + 1, (propertyPath.Length - (delimiterIndex + 1)));
if (firstPart.Length == 0)
{
return(SetPropertyValue(dtsObject, restOfString, value));
}
}
}
if (firstPart.ToUpper().StartsWith("PACKAGE"))
{
if (!(dtsObject is Package))
{
throw new ArgumentException("The initial object must be of type Package.", "dtsObject");
}
return(SetPropertyValue(dtsObject, restOfString, value));
}
// \Package.Variables[User::TestVar].Properties[Value]
if (firstPart.ToUpper().StartsWith("VARIABLES"))
{
if (!(dtsObject is DtsContainer))
{
throw new ArgumentException("Object must be of type DtsContainer to reference variables.", "dtsObject");
}
Variables vars = null;
string varName = GetSubStringBetween(firstPart, "[", "]");
DtsContainer cont = (DtsContainer)dtsObject;
cont.VariableDispenser.LockOneForRead(varName, ref vars);
returnValue = SetPropertyValue(vars[varName], restOfString, value);
vars.Unlock();
return(returnValue);
}
// \Package.Properties[CreationDate]
if (firstPart.ToUpper().StartsWith("PROPERTIES"))
{
if (!(dtsObject is IDTSPropertiesProvider))
{
throw new ArgumentException("Object must be of type IDTSPropertiesProvider to reference properties.", "dtsObject");
}
IDTSPropertiesProvider propProv = (IDTSPropertiesProvider)dtsObject;
string propIndex = GetSubStringBetween(firstPart, "[", "]");
DtsProperty prop = propProv.Properties[propIndex];
if (dtsObject is Variable && prop.Name == "Value")
{
Variable var = (Variable)dtsObject;
prop.SetValue(dtsObject, Convert.ChangeType(value, var.DataType));
}
else
{
prop.SetValue(dtsObject, Convert.ChangeType(value, propProv.Properties[propIndex].Type));
}
//Flag value as changing
changesvc.OnComponentChanging(prop, null);
changesvc.OnComponentChanged(prop, null, null, null); //marks the package designer as dirty
return(prop.GetValue(dtsObject));
}
// \Package.Connections[localhost.AdventureWorksDW2008].Properties[Description]
if (firstPart.ToUpper().StartsWith("CONNECTIONS"))
{
if (!(dtsObject is Package))
{
throw new ArgumentException("Object must be of type Package to reference Connections.", "dtsObject");
}
string connIndex = GetSubStringBetween(firstPart, "[", "]");
Package pkg = (Package)dtsObject;
return(SetPropertyValue(pkg.Connections[connIndex], restOfString, value));
}
// \Package.EventHandlers[OnError].Properties[Description]
if (firstPart.ToUpper().StartsWith("EVENTHANDLERS"))
{
if (!(dtsObject is EventsProvider))
{
throw new ArgumentException("Object must be of type EventsProvider to reference events.", "dtsObject");
}
EventsProvider eventProvider = (EventsProvider)dtsObject;
string eventIndex = GetSubStringBetween(firstPart, "[", "]");
return(SetPropertyValue(eventProvider.EventHandlers[eventIndex], restOfString, value));
}
//First Part of string is not one of the hard-coded values - it's either a task or container
if (!(dtsObject is IDTSSequence))
{
throw new ArgumentException("Object must be of type IDTSSequence to reference other tasks or containers.", "dtsObject");
}
IDTSSequence seq = (IDTSSequence)dtsObject;
if (seq.Executables.Contains(firstPart))
{
return(SetPropertyValue(seq.Executables[firstPart], restOfString, value));
}
// \Package\Sequence Container\Script Task.Properties[Description]
// \Package\Sequence Container.Properties[Description]
// \Package\Execute SQL Task.Properties[Description]
//\Package.EventHandlers[OnError].Variables[System::Cancel].Properties[Value]
// \Package.EventHandlers[OnError]\Script Task.Properties[Description]
if (restOfString.Length > 0)
{
returnValue = SetPropertyValue(dtsObject, restOfString, value);
}
return(returnValue);
}
private object LocatePropertyValue(object project, DtsObject dtsObject, string propertyPath, PropertyOperation operation, object value)
{
propertyPath = propertyPath.Replace("\\", ".");
object returnValue = null;
string firstPart = propertyPath;
string restOfString = string.Empty;
if (propertyPath.Contains("."))
{
// Can have periods in object names (like connection manager names)
// Need to verify that period is not between an index marker
int delimiterIndex = propertyPath.IndexOf(".", StringComparison.Ordinal);
if (delimiterIndex > propertyPath.IndexOf("[", StringComparison.Ordinal) &&
delimiterIndex < propertyPath.IndexOf("]", StringComparison.Ordinal))
{
delimiterIndex = propertyPath.IndexOf(".", propertyPath.IndexOf("]", StringComparison.Ordinal), StringComparison.Ordinal);
}
if (delimiterIndex > -1)
{
firstPart = propertyPath.Substring(0, delimiterIndex);
restOfString = propertyPath.Substring(delimiterIndex + 1, propertyPath.Length - (delimiterIndex + 1));
if (firstPart.Length == 0)
{
return(LocatePropertyValue(project, dtsObject, restOfString, operation, value));
}
}
}
//\Project\ConnectionManagers[localhost.AdventureWorks2012.conmgr].Properties[ConnectionString]
//\Project\Properties[ProtectionLevel]
if (firstPart.ToUpper().StartsWith("PROJECT"))
{
if (!(project is Project))
{
throw new ArgumentException("The initial object must be of type Project.", "project");
}
return(LocatePropertyValue(project, (DtsObject)project, restOfString, operation, value));
}
//\Project\ConnectionManagers[localhost.AdventureWorks2012.conmgr].Properties[ConnectionString]
if (firstPart.ToUpper().StartsWith("CONNECTIONMANAGERS"))
{
string connIndex = GetSubStringBetween(firstPart, "[", "]");
ConnectionManager cm = (((Project)project).ConnectionManagerItems[connIndex]).ConnectionManager;
return(LocatePropertyValue(project, cm, restOfString, operation, value));
}
if (firstPart.ToUpper().StartsWith("PACKAGE"))
{
if (!(dtsObject is Package))
{
throw new ArgumentException("The initial object must be of type Package.", "dtsObject");
}
return(LocatePropertyValue(project, dtsObject, restOfString, operation, value));
}
if (firstPart.ToUpper().StartsWith("VARIABLES"))
{
if (!(dtsObject is DtsContainer))
{
throw new ArgumentException("Object must be of type DtsContainer to reference variables.", "dtsObject");
}
Variables vars = null;
string varName = GetSubStringBetween(firstPart, "[", "]");
DtsContainer cont = (DtsContainer)dtsObject;
cont.VariableDispenser.LockOneForRead(varName, ref vars);
returnValue = LocatePropertyValue(project, vars[varName], restOfString, operation, value);
vars.Unlock();
return(returnValue);
}
// \Package.Properties[CreationDate]
if (firstPart.ToUpper().StartsWith("PROPERTIES"))
{
string propIndex = GetSubStringBetween(firstPart, "[", "]");
if (!(dtsObject is IDTSPropertiesProvider))
{
if (!(dtsObject is Project))
{
throw new ArgumentException("Object must be of type Project or IDTSPropertiesProvider to reference properties.", "dtsObject");
}
else
{
if (operation == PropertyOperation.Set)
{
dtsObject.GetType().GetProperty(propIndex).SetValue(dtsObject, Convert.ChangeType(value, dtsObject.GetType()));
}
return(dtsObject.GetType().GetProperty(propIndex).GetValue(dtsObject, null));
}
}
IDTSPropertiesProvider propProv = (IDTSPropertiesProvider)dtsObject;
DtsProperty prop = propProv.Properties[propIndex];
if (operation == PropertyOperation.Set)
{
if (dtsObject is Variable)
{
Variable var = (Variable)dtsObject;
prop.SetValue(dtsObject, Convert.ChangeType(value, var.DataType));
}
else
{
prop.SetValue(dtsObject, Convert.ChangeType(value, propProv.Properties[propIndex].Type));
}
}
return(prop.GetValue(dtsObject));
}
// \Package.Connections[localhost.AdventureWorksDW2008].Properties[Description]
if (firstPart.ToUpper().StartsWith("CONNECTIONS"))
{
if (!(dtsObject is Package))
{
throw new ArgumentException("Object must be of type Package to reference Connections.", "dtsObject");
}
string connIndex = GetSubStringBetween(firstPart, "[", "]");
Package pkg = (Package)dtsObject;
return(LocatePropertyValue(project, pkg.Connections[connIndex], restOfString, operation, value));
}
// \Package.EventHandlers[OnError].Properties[Description]
if (firstPart.ToUpper().StartsWith("EVENTHANDLERS"))
{
if (!(dtsObject is EventsProvider))
{
throw new ArgumentException("Object must be of type EventsProvider to reference events.", "dtsObject");
}
EventsProvider eventProvider = (EventsProvider)dtsObject;
string eventIndex = GetSubStringBetween(firstPart, "[", "]");
return(LocatePropertyValue(project, eventProvider.EventHandlers[eventIndex], restOfString, operation, value));
}
// First Part of string is not one of the hard-coded values - it's either a task or container
if (!(dtsObject is IDTSSequence))
{
throw new ArgumentException("Object must be of type IDTSSequence to reference other tasks or containers.", "dtsObject");
}
IDTSSequence seq = (IDTSSequence)dtsObject;
if (seq.Executables.Contains(firstPart))
{
return(LocatePropertyValue(project, seq.Executables[firstPart], restOfString, operation, value));
}
// \Package\Sequence Container\Script Task.Properties[Description]
// \Package\Sequence Container.Properties[Description]
// \Package\Execute SQL Task.Properties[Description]
// \Package.EventHandlers[OnError].Variables[System::Cancel].Properties[Value]
// \Package.EventHandlers[OnError]\Script Task.Properties[Description]
if (restOfString.Length > 0)
{
returnValue = LocatePropertyValue(project, dtsObject, restOfString, operation, value);
}
return(returnValue);
}
public DtsPropertyNodeFactory(DtsProperty property)
{
_property = property;
}
void expressionListWindow_EditExpressionSelected(object sender, EditExpressionSelectedEventArgs e)
{
try
{
Package package = null;
DtsContainer container = null;
if (win == null)
{
return;
}
try
{
package = GetCurrentPackage();
if (package == null)
{
return;
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.Assert(false, ex.ToString());
return;
}
// Parameters for Expression Editor
Variables variables = null;
VariableDispenser variableDispenser = null;
string propertyName = string.Empty;
Type propertyType = null;
// Target objects
IDTSPropertiesProvider propertiesProvider = null;
Variable variable = null;
PrecedenceConstraint constraint = null;
// Get the container
container = SSISHelpers.FindContainer(package, e.ContainerID);
// Get the property details and variable objects for the editor
if (e.Type == typeof(Variable))
{
variable = SSISHelpers.FindVariable(container, e.ObjectID);
propertyName = "Value";
propertyType = System.Type.GetType("System." + variable.DataType.ToString());
variables = container.Variables;
variableDispenser = container.VariableDispenser;
}
else if (e.Type == typeof(PrecedenceConstraint))
{
constraint = SSISHelpers.FindConstraint(container, e.ObjectID);
propertyName = "Expression";
propertyType = typeof(bool);
variables = container.Variables;
variableDispenser = container.VariableDispenser;
}
else
{
if (e.Type == typeof(ConnectionManager))
{
propertiesProvider = SSISHelpers.FindConnectionManager(package, e.ObjectID) as IDTSPropertiesProvider;
}
else if (e.Type == typeof(ForEachEnumerator))
{
ForEachLoop forEachLoop = container as ForEachLoop;
propertiesProvider = forEachLoop.ForEachEnumerator as IDTSPropertiesProvider;
}
else
{
propertiesProvider = container as IDTSPropertiesProvider;
}
if (propertiesProvider != null)
{
DtsProperty property = propertiesProvider.Properties[e.Property];
propertyName = property.Name;
propertyType = PackageHelper.GetTypeFromTypeCode(property.Type);
variables = container.Variables;
variableDispenser = container.VariableDispenser;
}
else
{
throw new Exception(string.Format(CultureInfo.InvariantCulture, "Expression editing not supported on this object ({0}).", e.ObjectID));
}
}
// Show the editor
Konesans.Dts.ExpressionEditor.ExpressionEditorPublic editor = new Konesans.Dts.ExpressionEditor.ExpressionEditorPublic(variables, variableDispenser, propertyType, propertyName, e.Expression);
editor.Editor.ExpressionFont = ExpressionFont;
editor.Editor.ExpressionColor = ExpressionColor;
editor.Editor.ResultFont = ResultFont;
editor.Editor.ResultColor = ResultColor;
if (editor.ShowDialog() == DialogResult.OK)
{
// Get expression
string expression = editor.Expression;
if (expression == null || string.IsNullOrEmpty(expression.Trim()))
{
expression = null;
}
// Set the new expression on the target object
object objectChanged = null;
if (variable != null)
{
if (expression == null)
{
variable.EvaluateAsExpression = false;
}
variable.Expression = expression;
objectChanged = variable;
}
else if (constraint != null)
{
if (expression == null)
{
constraint.EvalOp = DTSPrecedenceEvalOp.Constraint;
}
constraint.Expression = expression;
objectChanged = constraint;
}
else if (propertiesProvider != null)
{
// TaskHost, Sequence, ForLoop, ForEachLoop and ConnectionManager
propertiesProvider.SetExpression(e.Property, expression);
objectChanged = propertiesProvider;
}
expressionListWindow_RefreshExpressions(null, null);
// Finish displaying expressions list before you mark the package
// as dirty (which runs the expression highlighter)
System.Windows.Forms.Application.DoEvents();
SetPackageAsDirty(package, expression, objectChanged);
}
}
catch (Exception ex)
{
throw ex;
}
}
public object GetObjectFromPackagePath(string packagePath, out DtsProperty property)
{
return(Package.GetObjectFromPackagePath(packagePath, out property));
}
/// <summary>
/// A ctor that accepts the Microsoft.SqlServer.Dts.Runtime.DtsProperty object.
/// </summary>
/// <param name="property">the Microsoft.SqlServer.Dts.Runtime.DtsProperty object</param>
internal ISProperty(DtsProperty property)
{
Property = property;
}
public DtsPropertyNodeFactory(DtsProperty property)
{
_property = property;
}
public object GetObjectFromPackagePath(string packagePath, out DtsProperty property)
{
return Package.GetObjectFromPackagePath(packagePath, out property);
}
