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 PSADataFlow(EzContainer parent, PACKAGE_PSA p)
: base(parent)
{
this.p = p;
Source = new EzOleDbSource(this);
Source.Connection = this.p.Conns["Commercial_STG"];
Source.SqlCommand = String.Format("select * from {0}", this.p.tableName("STAGE") );
Source.Name = this.p.tableName("STAGE");
Console.WriteLine("Source Component created ...");
EzDerivedColumn AddAuditColumns = this.AddAuditColumns(Source);
Console.WriteLine("Derived Columns added ...");
EzLookup ExistingChecksum = this.ExistingChecksum(AddAuditColumns);
ExistingChecksum.NoMatchBehavor = NoMatchBehavior.SendToNoMatchOutput;
EzConditionalSplit CompareChecksums = this.CompareChecksums();
CompareChecksums.AttachTo(ExistingChecksum, 0, 0);
EzMultiCast Multicast = this.Multicast();
Multicast.AttachTo(CompareChecksums, 0, 0);
EzRowCount RowsUpdated = this.RowsUpdated();
RowsUpdated.AttachTo(Multicast, 0, 0);
EzDerivedColumn AddUpdateActionCode = this.AddUpdateActionCode();
AddUpdateActionCode.AttachTo(RowsUpdated);
EzRowCount RowsInserted = this.RowsInserted();
RowsInserted.AttachTo(ExistingChecksum, 1, 0);
EzDerivedColumn AddInsertActionCode = this.AddInsertActionCode();
AddInsertActionCode.AttachTo(RowsInserted);
MarkInactive = new EzOleDbDestination(this);
MarkInactive.Name = "Mark Inactive Staging Table";
MarkInactive.AttachTo(Multicast, 1, 0);
MarkInactive.Connection = this.p.Conns["Commercial_PSA"];
MarkInactive.Table = "psa_MarkInactive";
MarkInactive.LinkAllInputsToOutputs();
MarkInactive.ReinitializeMetaData();
UpdatedDestination = new EzOleDbDestination(this);
UpdatedDestination.Name = "PSA Data - Updates";
UpdatedDestination.AttachTo(AddUpdateActionCode);
UpdatedDestination.Connection = this.p.Conns["Commercial_PSA"];
UpdatedDestination.Table = this.p.tableName();
UpdatedDestination.LinkAllInputsToOutputs();
UpdatedDestination.ReinitializeMetaData();
InsertedDestination = new EzOleDbDestination(this);
InsertedDestination.Name = "PSA Data - Inserts";
InsertedDestination.AttachTo(AddInsertActionCode);
InsertedDestination.Connection = this.p.Conns["Commercial_PSA"];
InsertedDestination.Table = this.p.tableName();
InsertedDestination.LinkAllInputsToOutputs();
InsertedDestination.ReinitializeMetaData();
}
public EzMyPackage() : base()
{
// Connection managers
SrcConn = new EzSqlOleDbCM(this);
SrcConn.SetConnectionString(Environment.MachineName, "AdventureWorks");
MatchCM = new EzFlatFileCM(this);
MatchCM.ConnectionString = "matchcm.txt";
NoMatchCM = new EzFlatFileCM(this);
NoMatchCM.ConnectionString = "nomatchcm.txt";
ErrorCM = new EzFlatFileCM(this);
ErrorCM.ConnectionString = "errorcm.txt";
RefConn = new EzSqlOleDbCM(this);
RefConn.SetConnectionString(Environment.MachineName, "AdventureWorks");
// Creating Dataflow
Source = new EzOleDbSource(DataFlow);
Source.Connection = SrcConn;
Source.SqlCommand = "select * from HumanResources.Employee";
Lookup = new EzLookup(DataFlow);
Lookup.AttachTo(Source);
Lookup.OleDbConnection = RefConn;
Lookup.SqlCommand = "select * from HumanResources.EmployeeAddress";
Lookup.SetJoinCols("EmployeeID,EmployeeID");
Lookup.SetPureCopyCols("AddressID");
Lookup.NoMatchBehavor = NoMatchBehavior.SendToNoMatchOutput;
Lookup.OutputCol("AddressID").TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;
SortMatch = new EzSortTransform(DataFlow);
SortMatch.AttachTo(Lookup, 0, 0);
SortMatch.SortOrder["EmployeeID"] = 1; // sort in ascending order
SortMatch.SortOrder["AddressID"] = -2; // sort in descending order
SortNoMatch = new EzSortTransform(DataFlow);
SortNoMatch.AttachTo(Lookup, 1, 0);
SortNoMatch.SortOrder["EmployeeID"] = 1; // sort in ascending order
ErrorDest = new EzFlatFileDestination(DataFlow);
ErrorDest.AttachTo(Lookup, 2, 0);
ErrorDest.Connection = ErrorCM;
ErrorDest.DefineColumnsInCM(); // configure connection manager to have all input columns defined in the resulting file
MatchDest = new EzFlatFileDestination(DataFlow);
MatchDest.AttachTo(SortMatch);
MatchDest.Connection = MatchCM;
MatchDest.DefineColumnsInCM();
NoMatchDest = new EzFlatFileDestination(DataFlow);
NoMatchDest.AttachTo(SortNoMatch);
NoMatchDest.Connection = NoMatchCM;
NoMatchDest.DefineColumnsInCM();
}
public EzMyDataFlow(EzContainer parent) : base(parent)
{
// Connection managers
SrcConn = new EzSqlOleDbCM(Package, "SrcConn");
SrcConn.SetConnectionString(Environment.MachineName, "AdventureWorks");
MatchCM = new EzFlatFileCM(Package, "MatchCM");
MatchCM.ConnectionString = "matchcm.txt";
NoMatchCM = new EzFlatFileCM(Package, "NoMatchCM");
NoMatchCM.ConnectionString = "nomatchcm.txt";
ErrorCM = new EzFlatFileCM(Package, "ErrorCM");
ErrorCM.ConnectionString = "errorcm.txt";
RefConn = new EzSqlOleDbCM(Package, "RefConn");
RefConn.SetConnectionString(Environment.MachineName, "AdventureWorks");
// Creating Dataflow
Source = new EzOleDbSource(this);
Source.Connection = SrcConn;
Source.SqlCommand = "select * from HumanResources.Employee";
Lookup = new EzLookup(this);
Lookup.AttachTo(Source);
Lookup.OleDbConnection = RefConn;
Lookup.SqlCommand = "select * from HumanResources.EmployeeAddress";
Lookup.SetJoinCols("EmployeeID,EmployeeID");
Lookup.SetPureCopyCols("AddressID");
Lookup.NoMatchBehavor = NoMatchBehavior.SendToNoMatchOutput;
Lookup.OutputCol("AddressID").TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;
SortMatch = new EzSortTransform(this);
SortMatch.AttachTo(Lookup, 0, 0);
SortMatch.SortOrder["EmployeeID"] = 1; // sort in ascending order
SortMatch.SortOrder["AddressID"] = -2; // sort in descending order
SortNoMatch = new EzSortTransform(this);
SortNoMatch.AttachTo(Lookup, 1, 0);
SortNoMatch.SortOrder["EmployeeID"] = 1; // sort in ascending order
ErrorDest = new EzFlatFileDestination(this);
ErrorDest.AttachTo(Lookup, 2, 0);
ErrorDest.Connection = ErrorCM;
ErrorDest.DefineColumnsInCM(); // configure connection manager to have all input columns defined in the resulting file
MatchDest = new EzFlatFileDestination(this);
MatchDest.AttachTo(SortMatch);
MatchDest.Connection = MatchCM;
MatchDest.DefineColumnsInCM();
NoMatchDest = new EzFlatFileDestination(this);
NoMatchDest.AttachTo(SortNoMatch);
NoMatchDest.Connection = NoMatchCM;
NoMatchDest.DefineColumnsInCM();
}
public EzLookupPackage(string scrSvr, string scrDb, string dstSvr, string dstDb, string schemaAndTable, string[] lookupCondition)
: base()
{
this.Name = "TestLookupPackage";
this.DataFlow.Name = "Load New Rows";
//Set Connection Managers
ScrConn = new EzSqlOleDbCM(this);
ScrConn.SetConnectionString(scrSvr, scrDb);
ScrConn.Name = "Source";
DestConn = new EzSqlOleDbCM(this);
DestConn.SetConnectionString(dstSvr, dstDb);
DestConn.Name = "Destination";
//Create Dataflow
Source = new EzOleDbSource(DataFlow);
Source.Connection = ScrConn;
Source.Table = schemaAndTable;
//Source.SqlCommand = "SELECT * FROM dbo.T_Table1";
Source.Name = "Get rows from source";
//Configure Lookup
Lookup = new EzLookup(DataFlow);
Lookup.AttachTo(Source);
Lookup.OleDbConnection = DestConn;
Lookup.SqlCommand = "SELECT * FROM " + schemaAndTable;
Lookup.SetJoinCols(lookupCondition);
Lookup.NoMatchBehavor = NoMatchBehavior.SendToNoMatchOutput;
Lookup.Name = "Check against Destination";
//Send No Match Output to Destination
Destination = new EzOleDbDestination(DataFlow);
Destination.AttachTo(Lookup, 1, 0);
Destination.Connection = DestConn;
Destination.Table = schemaAndTable;
Destination.Name = "Send new rows to Destination";
}
public EzLookupPackage(string scrSvr, string scrDb, string dstSvr, string dstDb, string schemaAndTable, string[] lookupCondition)
: base()
{
this.Name = "TestLookupPackage";
this.DataFlow.Name = "Load New Rows";
//Set Connection Managers
ScrConn = new EzSqlOleDbCM(this);
ScrConn.SetConnectionString(scrSvr, scrDb);
ScrConn.Name = "Source";
DestConn = new EzSqlOleDbCM(this);
DestConn.SetConnectionString(dstSvr, dstDb);
DestConn.Name = "Destination";
//Create Dataflow
Source = new EzOleDbSource(DataFlow);
Source.Connection = ScrConn;
Source.Table = schemaAndTable;
//Source.SqlCommand = "SELECT * FROM dbo.T_Table1";
Source.Name = "Get rows from source";
//Configure Lookup
Lookup = new EzLookup(DataFlow);
Lookup.AttachTo(Source);
Lookup.OleDbConnection = DestConn;
Lookup.SqlCommand = "SELECT * FROM " + schemaAndTable;
Lookup.SetJoinCols(lookupCondition);
Lookup.NoMatchBehavor = NoMatchBehavior.SendToNoMatchOutput;
Lookup.Name = "Check against Destination";
//Send No Match Output to Destination
Destination = new EzOleDbDestination(DataFlow);
Destination.AttachTo(Lookup,1,0);
Destination.Connection = DestConn;
Destination.Table = schemaAndTable;
Destination.Name = "Send new rows to Destination";
}
static void Main(string[] args)
{
// Creating the package and the Data Flow Task:
EzPackage package = new EzPackage();
EzDataFlow dataFlow = new EzDataFlow(package);
// Creating a connection to the database:
EzSqlOleDbCM srcConn = Activator.CreateInstance(typeof(EzSqlOleDbCM), new object[] { package }) as EzSqlOleDbCM;
EzOleDbSource source = Activator.CreateInstance(typeof(EzOleDbSource), new object[] { dataFlow }) as EzOleDbSource;
source.Connection = srcConn;
srcConn.SetConnectionString("localhost", "Northwind");
source.Table = "FuzzyOrders";
// Creating six Flat File Destinations to write the output from the Conditional Split:
uint numberCases = 6;
List <EzFlatFileDestination> destinations = new List <EzFlatFileDestination>();
for (int i = 1; i <= numberCases; i++)
{
EzFlatFileCM conn = Activator.CreateInstance(typeof(EzFlatFileCM), new object[] { package }) as EzFlatFileCM;
EzFlatFileDestination dest = Activator.CreateInstance(typeof(EzFlatFileDestination), new object[] { dataFlow }) as EzFlatFileDestination;
dest.Connection = conn;
conn.ConnectionString = @"C:\CondSplit\output" + i + ".txt";
dest.Overwrite = true;
destinations.Add(dest);
}
// Attaching the Conditional Split to the source, and mapping input columns to output columns:
EzConditionalSplit split = new EzConditionalSplit(dataFlow);
split.AttachTo(source);
split.LinkAllInputsToOutputs();
/*
* Important step - this is where the new EzConditionalSplit functionality comes in.
*
* The Condition property of EzConditionalSplit indexes over outputs using output names.
* Providing an output name that does not yet exist will create a new output.
*
* The Condition property is set to a string that represents the condition that is evaluated to determine which
* output a particular row is sent to.
*
* The set method of the Condition property also assigns a string to the Expression property, which contains the actual string
* that is evaulated during runtime. In this string, column names are replaced with column IDs. The Expression property
* can be set directly, but the Condition property allows the user to provide a string containing column names without
* worrying about IDs. In addition, the Condition set method also assigns the new expression to be the next case to be evaluated,
* automatically setting the Order property, which must be sequential in order to guarantee execution.
*/
for (int i = 1; i <= numberCases; i++)
{
split.Condition["case" + i] = "EmployeeID == " + i;
}
// Attaching each destination to an output of Conditional Split:
for (int i = 1; i <= numberCases; i++)
{
destinations[i - 1].AttachTo(split, i + 1, 0);
destinations[i - 1].DefineColumnsInCM();
}
// Creating a destination for the default output:
EzFlatFileCM conn_default = Activator.CreateInstance(typeof(EzFlatFileCM), new object[] { package }) as EzFlatFileCM;
EzFlatFileDestination dest_default = Activator.CreateInstance(typeof(EzFlatFileDestination), new object[] { dataFlow }) as EzFlatFileDestination;
dest_default.Connection = conn_default;
conn_default.ConnectionString = @"C:\CondSplit\default.txt";
dest_default.Overwrite = true;
// Attaching the default destination to the default output.
dest_default.AttachTo(split, 0, 0);
dest_default.DefineColumnsInCM();
// Execute the package.
package.Execute();
}
/// <summary>
/// 创建 数据流任务
/// </summary>
/// <param name="parent"></param>
/// <param name="task"></param>
/// <param name="PreviousComponent"></param>
/// <returns></returns>
private EzDataFlow createEzDataFlow(EzContainer parent, Task task, EzExecutable PreviousComponent, Variables Variables)
{
//Adding a data flow task
EzDataFlow dataflow = new EzDataFlow(parent);
if (PreviousComponent != null)
{
dataflow.AttachTo(PreviousComponent);
}
dataflow.Name = task.TaskName + "_" + task.TaskId;
EzOleDbSource source = new EzOleDbSource(dataflow)
{
Name = task.TaskName,
//source.SqlCommand =string.Format("exec [dbo].[{0}] ",task.SourceTableName);
SqlCommand = task.SourceTableName,
Connection = ezOleDbConnectionManagers[task.SourceConmgrId],
//source.Table = task.SourceTableName;
AccessMode = AccessMode.AM_SQLCOMMAND
};
string start_guid = "";
string end_guid = "";
foreach (Variable x in Variables)
{
if (x.Namespace == "User")
{
if (x.Name == "StartTime" || x.Name == "EndTime")
{
if (x.Name == "StartTime")
{
start_guid = x.ID;
if (end_guid != "")
{
break;
}
}
else
{
end_guid = x.ID;
if (start_guid != "")
{
break;
}
}
}
}
}
source.SetComponentProperty("ParameterMapping", "\"@StartTime:Input\"," + start_guid + ";\"@EndTime:Input\"," + end_guid + ";");
//Adding an OLE DB Destination
EzOleDbDestination destination = new EzOleDbDestination(dataflow)
{
Name = task.TargetTableName,
Connection = ezOleDbConnectionManagers[task.TargetConmgrId],
AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD,
Table = task.TargetTableName
};
//Linking source and destination
destination.AttachTo(source);
destination.LinkAllInputsToOutputs();
return(dataflow);
}
static void Main(string[] args)
{
// Creating the package and data flow task:
EzPackage package = new EzPackage();
EzDataFlow dataFlow = new EzDataFlow(package);
// Creating the first source:
EzSqlOleDbCM srcConn1 = Activator.CreateInstance(typeof(EzSqlOleDbCM), new object[] { package }) as EzSqlOleDbCM;
EzOleDbSource source1 = Activator.CreateInstance(typeof(EzOleDbSource), new object[] { dataFlow }) as EzOleDbSource;
source1.Connection = srcConn1;
srcConn1.SetConnectionString("localhost", "Northwind");
source1.Table = "Customers";
// Creating the second source, which has the same columns as the first source:
EzSqlOleDbCM srcConn2 = Activator.CreateInstance(typeof(EzSqlOleDbCM), new object[] { package }) as EzSqlOleDbCM;
EzOleDbSource source2 = Activator.CreateInstance(typeof(EzOleDbSource), new object[] { dataFlow }) as EzOleDbSource;
source2.Connection = srcConn2;
srcConn2.SetConnectionString("localhost", "Northwind");
source2.Table = "Customers3";
// Creating the third source, which contains different columns to demonstrate how EzUnionAll handles this scenario:
EzSqlOleDbCM srcConn3 = Activator.CreateInstance(typeof(EzSqlOleDbCM), new object[] { package }) as EzSqlOleDbCM;
EzOleDbSource source3 = Activator.CreateInstance(typeof(EzOleDbSource), new object[] { dataFlow }) as EzOleDbSource;
source3.Connection = srcConn3;
srcConn3.SetConnectionString("localhost", "Northwind");
source3.Table = "FuzzyOrders";
/* Creating EzUnionAll and attaching it to the sources.
*
* The input columns of the first input attached define the output columns.
*
* The second input has the same output columns as the first input, so the input columns
* of the second input are mapped to the corresponding output columns.
*
* The third input shares two of the first input's columns, so these columns are automatically mapped together.
* The rest of the third input's columns do not match any of the existing output columns, so they are not mapped.
* No value is inserted into the unmapped output columns for rows that originate from the third input.
*/
EzUnionAll union = new EzUnionAll(dataFlow);
union.AttachTo(source1, 0, 0);
union.AttachTo(source2, 0, 1);
union.AttachTo(source3, 0, 2);
// Creating a Flat File Destination:
EzFlatFileCM output_conn = Activator.CreateInstance(typeof(EzFlatFileCM), new object[] { package }) as EzFlatFileCM;
EzFlatFileDestination output = Activator.CreateInstance(typeof(EzFlatFileDestination), new object[] { dataFlow }) as EzFlatFileDestination;
output.Connection = output_conn;
output_conn.ConnectionString = @"C:\Union All\UnionAllSample.txt";
output.Overwrite = true;
// Attaching the Union All component to the destination:
output.AttachTo(union, 0, 0);
output.DefineColumnsInCM();
// Executing the package:
package.Execute();
}
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();
}