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