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