public AstSlowlyChangingDimensionNode(IFrameworkItem parentAstNode)
: base(parentAstNode)
{
InitializeAstNode();
UnchangedPath = new AstDataflowOutputPathNode(this) { Name = "Unchanged", SsisName = UnchangedSsisName };
NewPath = new AstDataflowOutputPathNode(this) { Name = "New", SsisName = NewSsisName };
FixedAttributePath = new AstDataflowOutputPathNode(this) { Name = "FixedAttribute", SsisName = FixedAttributeSsisName };
ChangingAttributePath = new AstDataflowOutputPathNode(this) { Name = "ChangingAttribute", SsisName = ChangingAttributeSsisName };
HistoricalAttributePath = new AstDataflowOutputPathNode(this) { Name = "HistoricalAttribute", SsisName = HistoricalAttributeSsisName };
InferredMemberPath = new AstDataflowOutputPathNode(this) { Name = "InferredMember", SsisName = InferredMemberSsisName };
UnchangedPath.DefineSymbol();
NewPath.DefineSymbol();
FixedAttributePath.DefineSymbol();
ChangingAttributePath.DefineSymbol();
HistoricalAttributePath.DefineSymbol();
InferredMemberPath.DefineSymbol();
StaticOutputPaths.Add(UnchangedPath);
StaticOutputPaths.Add(NewPath);
StaticOutputPaths.Add(FixedAttributePath);
StaticOutputPaths.Add(ChangingAttributePath);
StaticOutputPaths.Add(HistoricalAttributePath);
StaticOutputPaths.Add(InferredMemberPath);
}
public AstDerivedColumnListNode(IFrameworkItem parentAstNode) : base(parentAstNode)
{
InitializeAstNode();
OutputPath = new AstDataflowOutputPathNode(this)
{
Name = "Output", SsisName = OutputSsisName
};
ErrorPath = new AstDataflowOutputPathNode(this)
{
Name = "Error", SsisName = ErrorSsisName
};
OutputPath.DefineSymbol();
ErrorPath.DefineSymbol();
PreferredOutputPath = OutputPath;
StaticOutputPaths.Add(OutputPath);
StaticOutputPaths.Add(ErrorPath);
foreach (var column in Columns)
{
if (!column.ReplaceExisting)
{
DefinedColumns.Add(new AstTransformationColumnNode(this)
{
ColumnName = column.Name
});
}
}
}
public AstLookupNode(IFrameworkItem parentAstNode) : base(parentAstNode)
{
InitializeAstNode();
MatchPath = new AstDataflowOutputPathNode(this)
{
Name = "Match", SsisName = MatchSsisName
};
NoMatchPath = new AstDataflowOutputPathNode(this)
{
Name = "NoMatch", SsisName = NoMatchSsisName
};
ErrorPath = new AstDataflowOutputPathNode(this)
{
Name = "Error", SsisName = ErrorSsisName
};
MatchPath.DefineSymbol();
NoMatchPath.DefineSymbol();
ErrorPath.DefineSymbol();
PreferredOutputPath = MatchPath;
StaticOutputPaths.Add(MatchPath);
StaticOutputPaths.Add(NoMatchPath);
StaticOutputPaths.Add(ErrorPath);
foreach (var column in Outputs)
{
DefinedColumns.Add(new AstTransformationColumnNode(this)
{
ColumnName = column.LocalColumnName
});
}
}
public AstOleDBCommandNode(IFrameworkItem parentAstNode) : base(parentAstNode)
{
InitializeAstNode();
OutputPath = new AstDataflowOutputPathNode(this)
{
Name = "Output", SsisName = OutputSsisName
};
ErrorPath = new AstDataflowOutputPathNode(this)
{
Name = "Error", SsisName = ErrorSsisName
};
OutputPath.DefineSymbol();
ErrorPath.DefineSymbol();
PreferredOutputPath = OutputPath;
StaticOutputPaths.Add(OutputPath);
StaticOutputPaths.Add(ErrorPath);
// TODO: Results?
////foreach (var column in Mappings)
////{
//// DefinedColumns.Add(new AstTransformationColumnNode(this) { ColumnName = column.DestinationName });
////}
}
public AstSortNode(IFrameworkItem parentAstNode)
: base(parentAstNode)
{
InitializeAstNode();
OutputPath = new AstDataflowOutputPathNode(this) { Name = "Output", SsisName = OutputSsisName };
OutputPath.DefineSymbol();
PreferredOutputPath = OutputPath;
StaticOutputPaths.Add(OutputPath);
}
public AstSortNode(IFrameworkItem parentAstNode) : base(parentAstNode)
{
InitializeAstNode();
OutputPath = new AstDataflowOutputPathNode(this)
{
Name = "Output", SsisName = OutputSsisName
};
OutputPath.DefineSymbol();
PreferredOutputPath = OutputPath;
StaticOutputPaths.Add(OutputPath);
}
public AstConditionalSplitNode(IFrameworkItem parentAstNode)
: base(parentAstNode)
{
InitializeAstNode();
ErrorPath = new AstDataflowOutputPathNode(this) { Name = "Error", SsisName = ErrorSsisName };
ErrorPath.DefineSymbol();
DefaultOutputPath = new AstDataflowOutputPathNode(this) { Name = "DefaultOutput", SsisName = "DefaultOutput" };
PreferredOutputPath = DefaultOutputPath;
StaticOutputPaths.Add(ErrorPath);
}
public AstLateArrivingLookupNode(IFrameworkItem parentAstNode)
: base(parentAstNode)
{
InitializeAstNode();
OutputPath = new AstDataflowOutputPathNode(this) { Name = "Output", SsisName = OutputSsisName };
OutputPath.DefineSymbol();
PreferredOutputPath = OutputPath;
StaticOutputPaths.Add(OutputPath);
foreach (var column in Outputs)
{
DefinedColumns.Add(new AstTransformationColumnNode(this) { ColumnName = column.LocalColumnName });
}
}
public AstSlowlyChangingDimensionNode(IFrameworkItem parentAstNode) : base(parentAstNode)
{
InitializeAstNode();
UnchangedPath = new AstDataflowOutputPathNode(this)
{
Name = "Unchanged", SsisName = UnchangedSsisName
};
NewPath = new AstDataflowOutputPathNode(this)
{
Name = "New", SsisName = NewSsisName
};
FixedAttributePath = new AstDataflowOutputPathNode(this)
{
Name = "FixedAttribute", SsisName = FixedAttributeSsisName
};
ChangingAttributePath = new AstDataflowOutputPathNode(this)
{
Name = "ChangingAttribute", SsisName = ChangingAttributeSsisName
};
HistoricalAttributePath = new AstDataflowOutputPathNode(this)
{
Name = "HistoricalAttribute", SsisName = HistoricalAttributeSsisName
};
InferredMemberPath = new AstDataflowOutputPathNode(this)
{
Name = "InferredMember", SsisName = InferredMemberSsisName
};
UnchangedPath.DefineSymbol();
NewPath.DefineSymbol();
FixedAttributePath.DefineSymbol();
ChangingAttributePath.DefineSymbol();
HistoricalAttributePath.DefineSymbol();
InferredMemberPath.DefineSymbol();
StaticOutputPaths.Add(UnchangedPath);
StaticOutputPaths.Add(NewPath);
StaticOutputPaths.Add(FixedAttributePath);
StaticOutputPaths.Add(ChangingAttributePath);
StaticOutputPaths.Add(HistoricalAttributePath);
StaticOutputPaths.Add(InferredMemberPath);
}
public AstConditionalSplitNode(IFrameworkItem parentAstNode) : base(parentAstNode)
{
InitializeAstNode();
ErrorPath = new AstDataflowOutputPathNode(this)
{
Name = "Error", SsisName = ErrorSsisName
};
ErrorPath.DefineSymbol();
DefaultOutputPath = new AstDataflowOutputPathNode(this)
{
Name = "DefaultOutput", SsisName = "DefaultOutput"
};
PreferredOutputPath = DefaultOutputPath;
StaticOutputPaths.Add(ErrorPath);
}
public AstOleDBCommandNode(IFrameworkItem parentAstNode)
: base(parentAstNode)
{
InitializeAstNode();
OutputPath = new AstDataflowOutputPathNode(this) { Name = "Output", SsisName = OutputSsisName };
ErrorPath = new AstDataflowOutputPathNode(this) { Name = "Error", SsisName = ErrorSsisName };
OutputPath.DefineSymbol();
ErrorPath.DefineSymbol();
PreferredOutputPath = OutputPath;
StaticOutputPaths.Add(OutputPath);
StaticOutputPaths.Add(ErrorPath);
// TODO: Results?
////foreach (var column in Mappings)
////{
//// DefinedColumns.Add(new AstTransformationColumnNode(this) { ColumnName = column.DestinationName });
////}
}
public AstLateArrivingLookupNode(IFrameworkItem parentAstNode) : base(parentAstNode)
{
InitializeAstNode();
OutputPath = new AstDataflowOutputPathNode(this)
{
Name = "Output", SsisName = OutputSsisName
};
OutputPath.DefineSymbol();
PreferredOutputPath = OutputPath;
StaticOutputPaths.Add(OutputPath);
foreach (var column in Outputs)
{
DefinedColumns.Add(new AstTransformationColumnNode(this)
{
ColumnName = column.LocalColumnName
});
}
}
public AstLookupNode(IFrameworkItem parentAstNode)
: base(parentAstNode)
{
InitializeAstNode();
MatchPath = new AstDataflowOutputPathNode(this) { Name = "Match", SsisName = MatchSsisName };
NoMatchPath = new AstDataflowOutputPathNode(this) { Name = "NoMatch", SsisName = NoMatchSsisName };
ErrorPath = new AstDataflowOutputPathNode(this) { Name = "Error", SsisName = ErrorSsisName };
MatchPath.DefineSymbol();
NoMatchPath.DefineSymbol();
ErrorPath.DefineSymbol();
PreferredOutputPath = MatchPath;
StaticOutputPaths.Add(MatchPath);
StaticOutputPaths.Add(NoMatchPath);
StaticOutputPaths.Add(ErrorPath);
foreach (var column in Outputs)
{
DefinedColumns.Add(new AstTransformationColumnNode(this) { ColumnName = column.LocalColumnName });
}
}
public AstDerivedColumnListNode(IFrameworkItem parentAstNode)
: base(parentAstNode)
{
InitializeAstNode();
OutputPath = new AstDataflowOutputPathNode(this) { Name = "Output", SsisName = OutputSsisName };
ErrorPath = new AstDataflowOutputPathNode(this) { Name = "Error", SsisName = ErrorSsisName };
OutputPath.DefineSymbol();
ErrorPath.DefineSymbol();
PreferredOutputPath = OutputPath;
StaticOutputPaths.Add(OutputPath);
StaticOutputPaths.Add(ErrorPath);
foreach (var column in Columns)
{
if (!column.ReplaceExisting)
{
DefinedColumns.Add(new AstTransformationColumnNode(this) { ColumnName = column.Name });
}
}
}
private static void BuildLateArrivingPath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdLateArrivingPath)
{
var inferredCommand = new AstOleDBCommandNode(parentItem)
{
Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InferredCommand"),
Connection = targetTable.Connection
};
inferredCommand.Query = new AstTransformationMappedQueryNode(inferredCommand);
int inferredIndex = 0;
var inferredSetColumnValueMappings = new List<TableColumnValueMapping>();
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && !column.IsAutoGenerated)
{
inferredCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(inferredCommand) { SourceName = column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", inferredIndex++) });
inferredSetColumnValueMappings.Add(new TableColumnValueMapping(column.Name, "?", MappingOperator.Assign));
}
}
if (targetTable.LateArriving)
{
inferredSetColumnValueMappings.Add(new TableColumnValueMapping("_LateArrived", "NULL", MappingOperator.Assign));
}
var inferredWhereColumnValueMappings = new List<TableColumnValueMapping>();
foreach (var keyColumn in targetTable.PreferredKey.Columns)
{
inferredCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(inferredCommand) { SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", inferredIndex++) });
inferredWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
}
if (targetTable.LateArriving)
{
inferredWhereColumnValueMappings.Add(new TableColumnValueMapping("_LateArrived", "NULL", MappingOperator.CompareIsNot));
}
inferredCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, inferredSetColumnValueMappings, inferredWhereColumnValueMappings);
inferredCommand.ValidateExternalMetadata = false;
inferredCommand.InputPath = new AstDataflowMappedInputPathNode(inferredCommand) { OutputPath = scdLateArrivingPath };
workflowFragment.Add(inferredCommand);
}
private static void BuildChangePath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdChangePath)
{
// Change Path should only be built if we actually have a column that takes a Type-1 Update
bool doUpdate = false;
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && column.ScdType == ScdType.Update && !column.IsAutoGenerated)
{
doUpdate = true;
break;
}
}
if (doUpdate)
{
var changeCommand = new AstOleDBCommandNode(parentItem)
{
Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_ChangeCommand"),
Connection = targetTable.Connection
};
changeCommand.Query = new AstTransformationMappedQueryNode(changeCommand);
int changeIndex = 0;
var changeSetColumnValueMappings = new List<TableColumnValueMapping>();
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && column.ScdType == ScdType.Update && !column.IsAutoGenerated)
{
changeCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(changeCommand) { SourceName = column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", changeIndex++) });
changeSetColumnValueMappings.Add(new TableColumnValueMapping(column.Name, "?", MappingOperator.Assign));
}
}
var changeWhereColumnValueMappings = new List<TableColumnValueMapping>();
foreach (var keyColumn in targetTable.PreferredKey.Columns)
{
changeCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(changeCommand) { SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", changeIndex++) });
changeWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
}
changeWhereColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "NULL", MappingOperator.CompareIs));
changeCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, changeSetColumnValueMappings, changeWhereColumnValueMappings);
changeCommand.ValidateExternalMetadata = false;
changeCommand.InputPath = new AstDataflowMappedInputPathNode(changeCommand) { OutputPath = scdChangePath };
workflowFragment.Add(changeCommand);
}
}
private static List<AstTransformationNode> CreateScdWorkflowFragment(AstTableNode targetTable, IFrameworkItem parentItem, AstDataflowOutputPathNode outputPath)
{
var workflowFragment = new List<AstTransformationNode>();
AstTableColumnBaseNode lateArrivingStatusColumn = targetTable.Columns.FirstOrDefault(item => item.Name == "_IsLate");
var scd = new AstSlowlyChangingDimensionNode(parentItem);
scd.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_scd");
scd.Connection = targetTable.Connection;
scd.CurrentRowWhere = "[_scdFrom] IS NOT NULL AND [_scdFrom] IS NULL";
scd.EnableInferredMember = targetTable.LateArriving;
scd.FailOnFixedAttributeChange = true;
scd.FailOnLookupFailure = false;
scd.IncomingRowChangeType = 1;
scd.InferredMemberIndicator = lateArrivingStatusColumn;
// TODO:
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && !column.IsAutoGenerated)
{
ScdColumnMappingType mappingType;
switch (column.ScdType)
{
case ScdType.Error:
mappingType = ScdColumnMappingType.FixedAttribute;
break;
case ScdType.Historical:
mappingType = ScdColumnMappingType.HistoricalAttribute;
break;
case ScdType.Key:
mappingType = ScdColumnMappingType.Key;
break;
case ScdType.Other:
mappingType = ScdColumnMappingType.Other;
break;
case ScdType.Update:
mappingType = ScdColumnMappingType.ChangingAttribute;
break;
default:
mappingType = ScdColumnMappingType.Other;
break;
}
scd.Mappings.Add(new AstScdTypeColumnMappingNode(scd) { MappingType = mappingType, QueryColumnName = column.Name });
}
}
scd.Query = TableLowerer.EmitSelectAllStatement(targetTable);
if (outputPath != null)
{
scd.InputPath = new AstDataflowMappedInputPathNode(scd) { OutputPath = outputPath };
}
workflowFragment.Add(scd);
// Late Arriving Path
if (targetTable.LateArriving)
{
BuildLateArrivingPath(targetTable, parentItem, workflowFragment, scd.InferredMemberPath);
}
// Change Path
BuildChangePath(targetTable, parentItem, workflowFragment, scd.ChangingAttributePath);
// Historical Path
var historicalOutput = BuildHistoricalSubpath(targetTable, parentItem, workflowFragment, scd.HistoricalAttributePath);
// Union Historical and New Paths
var insertUnionAll = new AstUnionAllNode(parentItem) { Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertUnionAll") };
insertUnionAll.InputPaths.Add(new AstDataflowMappedInputPathNode(insertUnionAll) { OutputPath = scd.NewPath });
insertUnionAll.InputPaths.Add(new AstDataflowMappedInputPathNode(insertUnionAll) { OutputPath = historicalOutput });
workflowFragment.Add(insertUnionAll);
// Insert Path
BuildInsertPath(targetTable, parentItem, workflowFragment, insertUnionAll.OutputPath);
return workflowFragment;
}
private static void BuildInsertPath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdInsertPath)
{
var insertTransform = new AstDerivedColumnListNode(parentItem) { Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertDerivedColumns") };
insertTransform.InputPath = new AstDataflowMappedInputPathNode(insertTransform) { OutputPath = scdInsertPath };
insertTransform.Columns.Add(new AstDerivedColumnNode(insertTransform)
{
Name = "_scdFrom",
Expression = "(DT_DBTIMESTAMP2,7)(@[System::StartTime])",
Scale = 7,
DerivedColumnType = ColumnType.DateTime2,
ReplaceExisting = false
});
insertTransform.Columns.Add(new AstDerivedColumnNode(insertTransform)
{
Name = "_scdTo",
Expression = "NULL(DT_DBTIMESTAMP2,7)",
Scale = 7,
DerivedColumnType = ColumnType.DateTime2,
ReplaceExisting = false
});
workflowFragment.Add(insertTransform);
var insertDestination = new AstDestinationNode(parentItem);
insertDestination.Table = targetTable;
insertDestination.DisableScd = true;
insertDestination.ValidateExternalMetadata = false;
insertDestination.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertDestination");
workflowFragment.Add(insertDestination);
}
private static AstDataflowOutputPathNode BuildHistoricalSubpath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdHistoricalPath)
{
var historicalTransform = new AstDerivedColumnListNode(parentItem);
historicalTransform.InputPath = new AstDataflowMappedInputPathNode(historicalTransform) { OutputPath = scdHistoricalPath };
historicalTransform.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_HistoricalDerivedColumns");
historicalTransform.Columns.Add(new AstDerivedColumnNode(historicalTransform)
{
Name = "_scdTo",
Expression = "(DT_DBTIMESTAMP2,7)(@[System::StartTime])",
Scale = 7,
DerivedColumnType = ColumnType.DateTime2,
ReplaceExisting = false
});
workflowFragment.Add(historicalTransform);
var historicalCommand = new AstOleDBCommandNode(parentItem)
{
Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_HistoricalCommand"),
Connection = targetTable.Connection
};
historicalCommand.Query = new AstTransformationMappedQueryNode(historicalCommand);
var historicalSetColumnValueMappings = new List<TableColumnValueMapping>();
var historicalWhereColumnValueMappings = new List<TableColumnValueMapping>();
historicalCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(historicalCommand) { SourceName = "_scdTo", TargetName = "Param_0" });
historicalSetColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "?", MappingOperator.Assign));
historicalWhereColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "NULL", MappingOperator.CompareIs));
int historicalIndex = 1;
foreach (var keyColumn in targetTable.PreferredKey.Columns)
{
historicalCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(historicalCommand) { SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", historicalIndex++) });
historicalWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
}
historicalCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, historicalSetColumnValueMappings, historicalWhereColumnValueMappings);
historicalCommand.ValidateExternalMetadata = false;
workflowFragment.Add(historicalCommand);
return historicalCommand.OutputPath;
}
