public override void Emit(XPathNavigator patternNavigator)
{
// Reloads invalidate the ParentContainer, so we should do it much later.
string etlName =
patternNavigator.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
Message.Trace(Severity.Notification, "{0}", etlName);
bool delayValidation = patternNavigator.SelectSingleNode("@DelayValidation").ValueAsBoolean;
string sourceName = patternNavigator.SelectSingleNode("rc:SourceConnection/@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
Connection sourceConnection =
Connection.GetExistingConnection(
VulcanPackage,
patternNavigator.SelectSingleNode("rc:SourceConnection/@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value
);
if (sourceConnection != null)
{
string query = patternNavigator.SelectSingleNode("rc:Query", VulcanPackage.VulcanConfig.NamespaceManager).Value.Trim();
// Add the Data Flow Task to the Package.
DTS.TaskHost pipeHost = (DTS.TaskHost)ParentContainer.Executables.Add("STOCK:PipelineTask");
pipeHost.Properties["DelayValidation"].SetValue(pipeHost, delayValidation);
MainPipe dataFlowTask = (MainPipe)pipeHost.InnerObject;
pipeHost.Name = etlName;
// Add the Source (this is temporary and will be replaced with a new style of Source element)
IDTSComponentMetaData90 sourceDataComponent = dataFlowTask.ComponentMetaDataCollection.New();
sourceDataComponent.ComponentClassID = "DTSAdapter.OleDbSource.1";
// IMPORTANT! If you do not Instantiate() and ProvideComponentProperties first,
// the component names do not get set... this is bad.
CManagedComponentWrapper oleInstance = sourceDataComponent.Instantiate();
oleInstance.ProvideComponentProperties();
sourceDataComponent.Name = etlName + " Source";
if (sourceDataComponent.RuntimeConnectionCollection.Count > 0)
{
sourceDataComponent.RuntimeConnectionCollection[0].ConnectionManager =
DTS.DtsConvert.ToConnectionManager90(
sourceConnection.ConnectionManager
);
sourceDataComponent.RuntimeConnectionCollection[0].ConnectionManagerID =
sourceConnection.ConnectionManager.ID;
}
oleInstance.SetComponentProperty("AccessMode", 2);
oleInstance.SetComponentProperty("SqlCommand", query);
try
{
oleInstance.AcquireConnections(null);
oleInstance.ReinitializeMetaData();
oleInstance.ReleaseConnections();
}
catch (System.Runtime.InteropServices.COMException ce)
{
Message.Trace(Severity.Error, ce, "OLEDBSource:{0}: {1}: Source {2}: Query {3}", sourceDataComponent.GetErrorDescription(ce.ErrorCode), ce.Message, sourceConnection.ConnectionManager.Name, query);
}
catch (Exception e)
{
Message.Trace(Severity.Error, e, "OLEDBSource:{0}: Source {1}: Query {2}", e.Message, sourceConnection.ConnectionManager.Name, query);
}
//Map parameter variables:
StringBuilder parameterBuilder = new StringBuilder();
foreach (XPathNavigator paramNav in patternNavigator.Select("rc:Parameter", VulcanPackage.VulcanConfig.NamespaceManager))
{
string name = paramNav.SelectSingleNode("@Name").Value;
string varName = paramNav.SelectSingleNode("@VariableName").Value;
if (VulcanPackage.DTSPackage.Variables.Contains(varName))
{
DTS.Variable variable = VulcanPackage.DTSPackage.Variables[varName];
parameterBuilder.AppendFormat("\"{0}\",{1};", name, variable.ID);
}
else
{
Message.Trace(Severity.Error, "DTS Variable {0} does not exist", varName);
}
}
oleInstance.SetComponentProperty("ParameterMapping", parameterBuilder.ToString());
///Transformation Factory
IDTSComponentMetaData90 parentComponent = sourceDataComponent;
XPathNavigator transNav = patternNavigator.SelectSingleNode("rc:Transformations", VulcanPackage.VulcanConfig.NamespaceManager);
if (transNav != null)
{
foreach (XPathNavigator nav in transNav.SelectChildren(XPathNodeType.Element))
{
// this is naughty but can be fixed later :)
Transformation t = TransformationFactory.ProcessTransformation(VulcanPackage, parentComponent, dataFlowTask, nav);
if (t != null)
{
parentComponent = t.Component;
}
}
}
XPathNavigator destNav = patternNavigator.SelectSingleNode("rc:Destination", VulcanPackage.VulcanConfig.NamespaceManager);
if (destNav != null)
{
string name = destNav.SelectSingleNode("@Name").Value;
Connection destConnection = Connection.GetExistingConnection(VulcanPackage, destNav.SelectSingleNode("@ConnectionName").Value);
string tableName = String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}", destNav.SelectSingleNode("@Table").Value.Trim());
OLEDBDestination oledbDestination = new OLEDBDestination(VulcanPackage, dataFlowTask, parentComponent, name, name, destConnection, tableName);
string accessMode = destNav.SelectSingleNode("@AccessMode").Value;
bool tableLock = destNav.SelectSingleNode("@TableLock").ValueAsBoolean;
bool checkConstraints = destNav.SelectSingleNode("@CheckConstraints").ValueAsBoolean;
bool keepIdentity = destNav.SelectSingleNode("@KeepIdentity").ValueAsBoolean;
bool keepNulls = destNav.SelectSingleNode("@KeepNulls").ValueAsBoolean;
int rowsPerBatch = destNav.SelectSingleNode("@RowsPerBatch").ValueAsInt;
int maxInsertCommitSize = destNav.SelectSingleNode("@MaximumInsertCommitSize").ValueAsInt;
switch (accessMode.ToUpperInvariant())
{
case "TABLE":
oledbDestination.ComponentInstance.SetComponentProperty("AccessMode", 0);
oledbDestination.ComponentInstance.SetComponentProperty("OpenRowset", tableName);
break;
case "TABLEFASTLOAD":
oledbDestination.ComponentInstance.SetComponentProperty("AccessMode", 3);
oledbDestination.ComponentInstance.SetComponentProperty("OpenRowset", tableName);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadKeepIdentity", keepIdentity);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadKeepNulls", keepNulls);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", maxInsertCommitSize);
StringBuilder fastLoadOptions = new StringBuilder();
if (tableLock)
{
fastLoadOptions.AppendFormat("TABLOCK,");
}
if (checkConstraints)
{
fastLoadOptions.AppendFormat("CHECK_CONSTRAINTS,");
}
if (rowsPerBatch > 0)
{
fastLoadOptions.AppendFormat("ROWS_PER_BATCH = {0}", rowsPerBatch);
}
fastLoadOptions = fastLoadOptions.Replace(",", "",fastLoadOptions.Length-5,5);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadOptions", fastLoadOptions.ToString());
break;
default:
Message.Trace(Severity.Error, "Unknown Destination Load Type of {0}", accessMode);
break;
}
try
{
oledbDestination.InitializeAndMapDestination();
}
catch (Exception)
{
}
// Map any overrides
foreach (XPathNavigator nav in destNav.Select("rc:Map", VulcanPackage.VulcanConfig.NamespaceManager))
{
string source = nav.SelectSingleNode("@Source").Value;
string destination;
bool unMap = false;
if (nav.SelectSingleNode("@Destination") == null)
{
unMap = true;
destination = source;
}
else
{
destination = nav.SelectSingleNode("@Destination").Value;
}
oledbDestination.Map(source, destination, unMap);
}
} // end DestNav != null
this.FirstExecutableGeneratedByPattern = pipeHost;
this.LastExecutableGeneratedByPattern = pipeHost;
} //END sourceConnection != null
else
{
Message.Trace(Severity.Error, "Source Connection {0} does not exist in {1}", sourceName, etlName);
}
}
private static Transformation CreateConditionalSplitFromXml(Vulcan.Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator conditionalSplitNav)
{
if (conditionalSplitNav == null || conditionalSplitNav.Name.ToUpperInvariant() != "ConditionalSplit".ToUpperInvariant())
{
return null;
}
string conditionalSplitName = conditionalSplitNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value;
Message.Trace(Severity.Debug, "Begin: ConditionalSplit Transformation {0}", conditionalSplitName);
ConditionalSplit cs = new ConditionalSplit(
vulcanPackage,
dataFlowTask,
parentComponent,
conditionalSplitName,
conditionalSplitName
);
int intEvaluationOrder = 0;
string expression = string.Empty;
foreach (XPathNavigator nav in conditionalSplitNav.Select("//rc:Output|//rc:DefaultOutput", vulcanPackage.VulcanConfig.NamespaceManager))
{
if (nav.Name == "DefaultOutput")
{
cs.Component.OutputCollection.SetIndex(cs.Component.OutputCollection.GetObjectIndexByID(cs.Component.OutputCollection["Conditional Split Default Output"].ID), 0);
}
else
{
expression = nav.SelectSingleNode("rc:Expression", vulcanPackage.VulcanConfig.NamespaceManager).Value;
IDTSOutput90 newPath = cs.Component.OutputCollection.New();
newPath.Name = nav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value;
newPath.Description = nav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value;
newPath.ExclusionGroup = cs.Component.OutputCollection["Conditional Split Default Output"].ExclusionGroup;
newPath.SynchronousInputID = cs.Component.OutputCollection["Conditional Split Default Output"].SynchronousInputID;
newPath.ErrorOrTruncationOperation = "Computation";
newPath.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
newPath.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSCustomProperty90 propEvaluationOrder = newPath.CustomPropertyCollection.New();
propEvaluationOrder.Name = "EvaluationOrder";
propEvaluationOrder.Value = intEvaluationOrder;
IDTSCustomProperty90 propFriendlyExpression = newPath.CustomPropertyCollection.New();
propFriendlyExpression.Name = "FriendlyExpression";
propFriendlyExpression.Value = expression;
IDTSCustomProperty90 propExpression = newPath.CustomPropertyCollection.New();
propExpression.Name = "Expression";
propExpression.Value = expression;
//A workaround to connect the path to Conditional Spit's output,
//because we always connect the current task to the previous task's first output
cs.Component.OutputCollection.SetIndex(cs.Component.OutputCollection.GetObjectIndexByID(newPath.ID), 0);
intEvaluationOrder++;
}
IDTSComponentMetaData90 startComponent = cs.Component;
XPathNavigator transNav = nav.SelectSingleNode("rc:ConditionalSplitOutputPath/rc:Transformations", vulcanPackage.VulcanConfig.NamespaceManager);
if (transNav != null)
{
foreach (XPathNavigator etlNav in transNav.SelectChildren(XPathNodeType.Element))
{
// this is naughty but can be fixed later :)
Transformation t = TransformationFactory.ProcessTransformation(vulcanPackage, startComponent, dataFlowTask, etlNav);
if (t != null)
{
startComponent = t.Component;
}
}
}
XPathNavigator destNav = nav.SelectSingleNode("rc:ConditionalSplitOutputPath/rc:Destination", vulcanPackage.VulcanConfig.NamespaceManager);
if (destNav != null)
{
string name = destNav.SelectSingleNode("@Name").Value;
Connection destConnection = Connection.GetExistingConnection(vulcanPackage, destNav.SelectSingleNode("@ConnectionName").Value);
string tableName = String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}", destNav.SelectSingleNode("@Table").Value.Trim());
OLEDBDestination oledbDestination = new OLEDBDestination(vulcanPackage, dataFlowTask, startComponent, name, name, destConnection, tableName);
string accessMode = destNav.SelectSingleNode("@AccessMode").Value;
bool tableLock = destNav.SelectSingleNode("@TableLock").ValueAsBoolean;
bool checkConstraints = destNav.SelectSingleNode("@CheckConstraints").ValueAsBoolean;
bool keepIdentity = destNav.SelectSingleNode("@KeepIdentity").ValueAsBoolean;
bool keepNulls = destNav.SelectSingleNode("@KeepNulls").ValueAsBoolean;
int rowsPerBatch = destNav.SelectSingleNode("@RowsPerBatch").ValueAsInt;
int maxInsertCommitSize = destNav.SelectSingleNode("@MaximumInsertCommitSize").ValueAsInt;
switch (accessMode.ToUpperInvariant())
{
case "TABLE":
oledbDestination.ComponentInstance.SetComponentProperty("AccessMode", 0);
oledbDestination.ComponentInstance.SetComponentProperty("OpenRowset", tableName);
break;
case "TABLEFASTLOAD":
oledbDestination.ComponentInstance.SetComponentProperty("AccessMode", 3);
oledbDestination.ComponentInstance.SetComponentProperty("OpenRowset", tableName);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadKeepIdentity", keepIdentity);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadKeepNulls", keepNulls);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", maxInsertCommitSize);
StringBuilder fastLoadOptions = new StringBuilder();
if (tableLock)
{
fastLoadOptions.AppendFormat("TABLOCK,");
}
if (checkConstraints)
{
fastLoadOptions.AppendFormat("CHECK_CONSTRAINTS,");
}
if (rowsPerBatch > 0)
{
fastLoadOptions.AppendFormat("ROWS_PER_BATCH = {0}", rowsPerBatch);
}
fastLoadOptions = fastLoadOptions.Replace(",", "", fastLoadOptions.Length - 5, 5);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadOptions", fastLoadOptions.ToString());
break;
default:
Message.Trace(Severity.Error, "Unknown Destination Load Type of {0}", accessMode);
break;
}
try
{
oledbDestination.InitializeAndMapDestination();
}
catch (Exception)
{
}
// Map any overrides
foreach (XPathNavigator navMap in destNav.Select("rc:Map", vulcanPackage.VulcanConfig.NamespaceManager))
{
string source = navMap.SelectSingleNode("@Source").Value;
string destination;
bool unMap = false;
if (navMap.SelectSingleNode("@Destination") == null)
{
unMap = true;
destination = source;
}
else
{
destination = nav.SelectSingleNode("@Destination").Value;
}
oledbDestination.Map(source, destination, unMap);
}
} // end DestNav != null
//this.FirstExecutableGeneratedByPattern = pipeHost;
//this.LastExecutableGeneratedByPattern = pipeHost;
}
return cs;
}
private static Transformation CreateConditionalSplitFromXml(Vulcan.Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator conditionalSplitNav)
{
if (conditionalSplitNav == null || conditionalSplitNav.Name.ToUpperInvariant() != "ConditionalSplit".ToUpperInvariant())
{
return(null);
}
string conditionalSplitName = conditionalSplitNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value;
Message.Trace(Severity.Debug, "Begin: ConditionalSplit Transformation {0}", conditionalSplitName);
ConditionalSplit cs = new ConditionalSplit(
vulcanPackage,
dataFlowTask,
parentComponent,
conditionalSplitName,
conditionalSplitName
);
int intEvaluationOrder = 0;
string expression = string.Empty;
foreach (XPathNavigator nav in conditionalSplitNav.Select("//rc:Output|//rc:DefaultOutput", vulcanPackage.VulcanConfig.NamespaceManager))
{
if (nav.Name == "DefaultOutput")
{
cs.Component.OutputCollection.SetIndex(cs.Component.OutputCollection.GetObjectIndexByID(cs.Component.OutputCollection["Conditional Split Default Output"].ID), 0);
}
else
{
expression = nav.SelectSingleNode("rc:Expression", vulcanPackage.VulcanConfig.NamespaceManager).Value;
IDTSOutput90 newPath = cs.Component.OutputCollection.New();
newPath.Name = nav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value;
newPath.Description = nav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value;
newPath.ExclusionGroup = cs.Component.OutputCollection["Conditional Split Default Output"].ExclusionGroup;
newPath.SynchronousInputID = cs.Component.OutputCollection["Conditional Split Default Output"].SynchronousInputID;
newPath.ErrorOrTruncationOperation = "Computation";
newPath.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
newPath.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSCustomProperty90 propEvaluationOrder = newPath.CustomPropertyCollection.New();
propEvaluationOrder.Name = "EvaluationOrder";
propEvaluationOrder.Value = intEvaluationOrder;
IDTSCustomProperty90 propFriendlyExpression = newPath.CustomPropertyCollection.New();
propFriendlyExpression.Name = "FriendlyExpression";
propFriendlyExpression.Value = expression;
IDTSCustomProperty90 propExpression = newPath.CustomPropertyCollection.New();
propExpression.Name = "Expression";
propExpression.Value = expression;
//A workaround to connect the path to Conditional Spit's output,
//because we always connect the current task to the previous task's first output
cs.Component.OutputCollection.SetIndex(cs.Component.OutputCollection.GetObjectIndexByID(newPath.ID), 0);
intEvaluationOrder++;
}
IDTSComponentMetaData90 startComponent = cs.Component;
XPathNavigator transNav = nav.SelectSingleNode("rc:ConditionalSplitOutputPath/rc:Transformations", vulcanPackage.VulcanConfig.NamespaceManager);
if (transNav != null)
{
foreach (XPathNavigator etlNav in transNav.SelectChildren(XPathNodeType.Element))
{
// this is naughty but can be fixed later :)
Transformation t = TransformationFactory.ProcessTransformation(vulcanPackage, startComponent, dataFlowTask, etlNav);
if (t != null)
{
startComponent = t.Component;
}
}
}
XPathNavigator destNav = nav.SelectSingleNode("rc:ConditionalSplitOutputPath/rc:Destination", vulcanPackage.VulcanConfig.NamespaceManager);
if (destNav != null)
{
string name = destNav.SelectSingleNode("@Name").Value;
Connection destConnection = Connection.GetExistingConnection(vulcanPackage, destNav.SelectSingleNode("@ConnectionName").Value);
string tableName = String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}", destNav.SelectSingleNode("@Table").Value.Trim());
OLEDBDestination oledbDestination = new OLEDBDestination(vulcanPackage, dataFlowTask, startComponent, name, name, destConnection, tableName);
string accessMode = destNav.SelectSingleNode("@AccessMode").Value;
bool tableLock = destNav.SelectSingleNode("@TableLock").ValueAsBoolean;
bool checkConstraints = destNav.SelectSingleNode("@CheckConstraints").ValueAsBoolean;
bool keepIdentity = destNav.SelectSingleNode("@KeepIdentity").ValueAsBoolean;
bool keepNulls = destNav.SelectSingleNode("@KeepNulls").ValueAsBoolean;
int rowsPerBatch = destNav.SelectSingleNode("@RowsPerBatch").ValueAsInt;
int maxInsertCommitSize = destNav.SelectSingleNode("@MaximumInsertCommitSize").ValueAsInt;
switch (accessMode.ToUpperInvariant())
{
case "TABLE":
oledbDestination.ComponentInstance.SetComponentProperty("AccessMode", 0);
oledbDestination.ComponentInstance.SetComponentProperty("OpenRowset", tableName);
break;
case "TABLEFASTLOAD":
oledbDestination.ComponentInstance.SetComponentProperty("AccessMode", 3);
oledbDestination.ComponentInstance.SetComponentProperty("OpenRowset", tableName);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadKeepIdentity", keepIdentity);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadKeepNulls", keepNulls);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", maxInsertCommitSize);
StringBuilder fastLoadOptions = new StringBuilder();
if (tableLock)
{
fastLoadOptions.AppendFormat("TABLOCK,");
}
if (checkConstraints)
{
fastLoadOptions.AppendFormat("CHECK_CONSTRAINTS,");
}
if (rowsPerBatch > 0)
{
fastLoadOptions.AppendFormat("ROWS_PER_BATCH = {0}", rowsPerBatch);
}
fastLoadOptions = fastLoadOptions.Replace(",", "", fastLoadOptions.Length - 5, 5);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadOptions", fastLoadOptions.ToString());
break;
default:
Message.Trace(Severity.Error, "Unknown Destination Load Type of {0}", accessMode);
break;
}
try
{
oledbDestination.InitializeAndMapDestination();
}
catch (Exception)
{
}
// Map any overrides
foreach (XPathNavigator navMap in destNav.Select("rc:Map", vulcanPackage.VulcanConfig.NamespaceManager))
{
string source = navMap.SelectSingleNode("@Source").Value;
string destination;
bool unMap = false;
if (navMap.SelectSingleNode("@Destination") == null)
{
unMap = true;
destination = source;
}
else
{
destination = nav.SelectSingleNode("@Destination").Value;
}
oledbDestination.Map(source, destination, unMap);
}
} // end DestNav != null
//this.FirstExecutableGeneratedByPattern = pipeHost;
//this.LastExecutableGeneratedByPattern = pipeHost;
}
return(cs);
}
