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);
}
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);
}
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);
}