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 EzDerivedColumn AddAuditColumns(EzComponent attacher)
{
EzDerivedColumn component = new EzDerivedColumn(this);
component.AttachTo(attacher);
component.Name = "Add Audit Columns";
component.Expression["CreatedDate"] = "GETDATE()";
component.Expression["ActiveFlag"] = "(DT_STR,1,1252)\"Y\"";
return component;
}
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();
}