public FactDataFlow(EzContainer parent, PACKAGE_FACT p, SOURCEOBJECT so) : base(parent) { this.p = p; this.so = so; p.m.SOURCEOBJECTS[0]. EzOleDbSource Source = new EzOleDbSource(this); Source.Connection = p.Conns["Source"]; //TODO correct query Source.SqlCommand = String.Format("select * from {0} where ActiveFlag = 'Y' and CreatedDate > '01-01-1900'", so.DATAOBJECT.tableName("PSA")); Source. Source.Name = p.m.NAME; WrapperObject A = new WrapperObject(); A.LastStep = (EzComponent) Source; //TODO for each lookup, write lookup query, output EzLookup Lookup_1 = new EzLookup(this); Lookup_1.AttachTo(A.LastStep); EzRowCount RowsInserted = new EzRowCount(this); RowsInserted.Name = "Rows Inserted"; RowsInserted.VariableName = "Audit::RowsInserted"; RowsInserted.AttachTo(Lookup_1); EzOleDbDestination FactDestination = new EzOleDbDestination(this); FactDestination.Name =this.p.tableName(); FactDestination.AttachTo(RowsInserted); FactDestination.Connection = p.Conns["FACT"]; FactDestination.Table = p.tableName(); FactDestination.LinkAllInputsToOutputs(); FactDestination.ReinitializeMetaData(); }
public StageDataFlow(EzContainer parent, PACKAGE_STAGE p) : base(parent) { this.p = p; Source = new EzOleDbSource(this); Source.Connection = p.Conns["Source"]; //TODO allow custom query Source.SqlCommand = p.d.SOURCEQUERY; Source.Name = p.d.NAME; Console.WriteLine("Source Component created ..."); EzDerivedColumn CreateNaturalKey = new EzDerivedColumn(this); CreateNaturalKey.AttachTo(Source); CreateNaturalKey.Name = "Create Natural Key"; //TODO split NaturalKey from NaturalKeyColumnName, generate NaturalKey expression programmatically CreateNaturalKey.Expression["NaturalKey"] = this.getNaturalKey(); CreateNaturalKey.Expression["NaturalKeyColumnName"] = this.getNaturalKeyColumnName(); CreateNaturalKey.Expression["CreateDate"] = "GETDATE()"; CreateNaturalKey.Expression["StageTableName"] = "(DT_STR,150,1252)\"" + p.tableName("STAGE") + "\""; //TODO if dataobject is dimension type, get dataset from dimension CreateNaturalKey.Expression["DataSetName"] = "(DT_STR,150,1252)\"" + p.d.MATCHDATASET + "\""; Console.WriteLine("Derived Columns added ..."); GetDatasetID = new EzLookup(this); GetDatasetID.Name = "Get Dataset ID"; GetDatasetID.AttachTo(CreateNaturalKey); GetDatasetID.SetJoinCols("DataSetName,DataSetName"); GetDatasetID.OleDbConnection = this.p.Conns["MATCH"]; GetDatasetID.SqlCommand = "select DataSetName, DataSetID from DataSets"; GetDatasetID.SetCopyOverwriteCols("DataSetID,DataSetID"); Console.WriteLine("DataSet ID Acquired ..."); GetStageTableID = new EzLookup(this); GetStageTableID.Name = "Get Stage Table ID"; GetStageTableID.AttachTo(GetDatasetID); GetStageTableID.SetJoinCols("StageTableName,TableName"); GetStageTableID.OleDbConnection = p.Conns["Commercial_META"]; //TODO better query when more tables are in it table GetStageTableID.SqlCommand = "select TableName, TableID from meta_Table"; GetStageTableID.SetCopyOverwriteCols("Stage_TableID,TableID"); Console.WriteLine("Stage Table ID Acquired..."); //TODO if StageTableID is 0, insert and return value GenerateChecksum = new EzChecksum(this); GenerateChecksum.Name = "Generate Checksum"; GenerateChecksum.LinkAllInputsToOutputs(); GenerateChecksum.ReinitializeMetaData(); GenerateChecksum.AttachTo(GetStageTableID); AssignMatchKey = new EzLookup(this); AssignMatchKey.Name = "Assign Match Key"; AssignMatchKey.AttachTo(GenerateChecksum); AssignMatchKey.SetJoinCols("NaturalKey,NaturalKey"); AssignMatchKey.OleDbConnection = p.Conns["MATCH"]; AssignMatchKey.SqlCommand = "SELECT NaturalKey, MatchKey from " + this.p.tableName("MATCH"); AssignMatchKey.SetCopyOverwriteCols("MatchKey,MatchKey"); AssignMatchKey.NoMatchBehavor = NoMatchBehavior.SendToNoMatchOutput; Console.WriteLine("Matchkey checked ..."); RowsMatched = new EzRowCount(this); RowsMatched.Name = "Rows Matched"; RowsMatched.VariableName = "Audit::RowsMatched"; RowsMatched.AttachTo(AssignMatchKey, 0, 0); MatchedDestination = new EzOleDbDestination(this); MatchedDestination.Name = this.p.tableName("STAGE"); MatchedDestination.AttachTo(RowsMatched); MatchedDestination.Connection = this.p.Conns["Commercial_STG"]; MatchedDestination.Table = this.p.tableName("STAGE"); MatchedDestination.LinkAllInputsToOutputs(); MatchedDestination.ReinitializeMetaData(); Console.WriteLine("Matched Destination created ..."); RowsInserted = new EzRowCount(this); RowsInserted.Name = "Rows Inserted"; RowsInserted.VariableName = "Audit::RowsInserted"; RowsInserted.AttachTo(AssignMatchKey, 1, 0); RetrieveLastMatchKey = new EzLookup(this); RetrieveLastMatchKey.Name = "Retrieve Last MatchKey"; RetrieveLastMatchKey.AttachTo(RowsInserted); RetrieveLastMatchKey.SetJoinCols("DataSetID,DataSetID"); RetrieveLastMatchKey.OleDbConnection = this.p.Conns["MATCH"]; RetrieveLastMatchKey.SqlCommand = String.Format("SELECT DataSetID, MAX(MatchKey) as MaxMatchKey FROM {0} GROUP BY DataSetID", this.p.tableName("MATCH")); RetrieveLastMatchKey.SetCopyOverwriteCols("MaxMatchKey,MaxMatchKey"); GenerateMatchKey = new EzRowNumber(this); GenerateMatchKey.Name = "Generate MatchKey"; GenerateMatchKey.AttachTo(RetrieveLastMatchKey); InsertNewMatchKey = new EzDerivedColumn(this); InsertNewMatchKey.Name = "Insert New MatchKey"; InsertNewMatchKey.AttachTo(GenerateMatchKey); InsertNewMatchKey.Expression["MatchKey"] = "ISNULL(MaxMatchKey) == TRUE ? RowNumber : MaxMatchKey + RowNumber"; Console.WriteLine("New Matchkey component created ..."); MultiCast = new EzMultiCast(this); MultiCast.Name = "MultiCast"; MultiCast.AttachTo(InsertNewMatchKey); InsertedDestination = new EzOleDbDestination(this); InsertedDestination.Name =String.Format("{0} - New Records", this.p.tableName("STAGE")); InsertedDestination.AttachTo(MultiCast, 0, 0); InsertedDestination.Connection = p.Conns["Commercial_STG"]; InsertedDestination.Table = p.tableName("STAGE"); InsertedDestination.LinkAllInputsToOutputs(); InsertedDestination.ReinitializeMetaData(); MasterMatch = new EzOleDbDestination(this); MasterMatch.Name = this.p.tableName("MATCH"); MasterMatch.AttachTo(MultiCast, 1, 0); MasterMatch.Connection = p.Conns["MATCH"]; MasterMatch.ReinitializeMetaData(); MasterMatch.Table = this.p.tableName("MATCH"); MasterMatch.LinkAllInputsToOutputs(); Console.WriteLine("Mastermatch Destination created ..."); }
public DimDataFlow(EzContainer parent, PACKAGE_DIM p, SOURCEOBJECT so) : base(parent) { this.p = p; this.so = so; EzOleDbSource Source = new EzOleDbSource(this); Source.Connection = p.Conns["Source"]; Source.SqlCommand = String.Format("select * from {0} where ActiveFlag = 'Y' and CreatedDate > '01-01-1900'", so.DATAOBJECT.tableName("PSA")); Source.Name = so.DATAOBJECT.tableName("PSA"); EzDerivedColumn DeriveAttributes = new EzDerivedColumn(this); DeriveAttributes.AttachTo(Source); DeriveAttributes.Name = "Derive Attributes"; //TODO for each mapping column add attribute expression foreach (MAPPINGCOLUMN mappingColumn in so.MAPPINGCOLUMNS) { if (mappingColumn.ATTRIBUTE == null) { mappingColumn.ATTRIBUTE = mappingColumn.DATACOLUMN; } //TODO based on attribute type determine what sort of SSIS-ifying the data column needs DeriveAttributes.Expression[mappingColumn.ATTRIBUTE] = "(DT_STR,150,1252)\"" + mappingColumn.DATACOLUMN + "\""; } EzConditionalSplit ActionCode = new EzConditionalSplit(this); ActionCode.AttachTo(DeriveAttributes); ActionCode.Condition["case1"] = "ActionCode == 'UPDATE'"; ActionCode.Condition["case2"] = "ActionCode == 'INSERT'"; EzRowCount RowsMatched = new EzRowCount(this); RowsMatched.Name = "Rows Matched"; RowsMatched.VariableName = "Audit::RowsMatched"; RowsMatched.AttachTo(ActionCode, 0, 0); //TODO correct ole db command query, parameter mapping EzOleDbCommand UpdateDimension = new EzOleDbCommand(this); UpdateDimension.AttachTo(RowsMatched); EzRowCount RowsInserted = new EzRowCount(this); RowsInserted.Name = "Rows Inserted"; RowsInserted.VariableName = "Audit::RowsInserted"; RowsInserted.AttachTo(ActionCode, 1, 0); EzOleDbDestination InsertedDestination = new EzOleDbDestination(this); InsertedDestination.Name =String.Format("{0} - New Records", this.p.tableName()); InsertedDestination.AttachTo(RowsInserted); InsertedDestination.Connection = p.Conns["DIM"]; InsertedDestination.Table = p.tableName(); InsertedDestination.LinkAllInputsToOutputs(); InsertedDestination.ReinitializeMetaData(); }