Hi, Given that you want to record whether the rows were transferred (presumably so you can do incremental transfers?), I'd go with transferring the two tables independently, i.e. option 3 below, because it's just a simpler process overall (IMHO).
By adding a "transferred" column to *both* tables, it's trivial to pull out the rows in the OrderLines table that have not been transferred, and which correspond to orders that have. In this situation, I'd also go for a counter to identify which rows were transferred in which run, rather than a simple yes/no flag. Then you can rollback easily if required, check row counts for discrepancies in each db etc etc. You can also do the transfer "in bulk", rather than order-by-order, which would speed things up. Miles On Thu, Dec 16, 2010 at 7:44 PM, Jason Meckley <[email protected]>wrote: > you can join > 1. in the sql statement itself > 2. as a JoinOperation you implement > 3. transfer orders and then order lines independent of each other. > > option 1 will require branching and aggregation to properly insert the > orders and order lines to the destination > option 2 doesn't make much sense given the data resides in the same > database > option 3 would work, but you would need to track which order lines to > pull after inserting the orders. > > option 1 might look like this > > Register(new ConventionInputCommand("source"){Command = "select * from > header left join lines ..."}); > Register(new BranchingOperation() > .Add(Partial > .Register(new GroupHeader()) // > implements abstractaggreateoperation > .Register(new > ConventionOutputComand("destionation"){Command = "insert into > header ...")) > .Add(new ConventionOutputComand("destionation") > {Command = "insert into orderlines ...")); > > > option 3 might look like this > List<object> orderIds = new List<object>() > Register(new ConventionInputCommand("source"){Command = "select * from > header ..."}); > Register(new CollectOrdeIds(orderIds)); > Register(new ConventionInputCommand("source"){Command = "insert into > header ..."}); > > void PostProcessing() > { > if(GetAllErrors().Any()) return; > > using(var process = new TransferOrderLines()) > { > process.OrderIds = orderIds; > process.Execute(); > } > } > > class CollectOrdeIds : AbstractOperation > { > private readonly ICollection<object> ordeIds; > > public CollectOrdeIds(ICollection<object> ordeIds) > { > this.orderIds = orderIds; > } > > public IEnumerable<Row> Execute(IEnumerable<Row> rows) > { > foreach(var row in rows) > { > orderIds.Add(row["id"]); > yield return row; > } > } > } > > On Dec 16, 9:43 am, Alex Brown <[email protected]> wrote: > > Hi > > I have a db with a structure similar to: > > > > Orders > > - ID > > - CustomerName > > - CustomerEmailAddress > > - DeliveryAddress > > - Transferred > > > > OrderLine > > - OrderID > > - ProductName > > - Price > > > > (you get the idea) > > > > I'm trying to create an ETL process using Rhino ETL that can copy > > these from ServerA sql server db to ServerB > > > > ServerB tables are identical, so there is no Transform to do as such. > > > > The only thing I need to do is after the transfer of each order (and > > it's corresponding OrderLine) is set Transferred to 1 (True) on the > > Orders table for that row. > > > > I've started this... but can't work out how to effectively Join the > > tables..? > > Is this something that can be done? Or should I have a separate > > process (and another Transferred column) on the OrderLine table? > > -- > You received this message because you are subscribed to the Google Groups > "Rhino Tools Dev" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<rhino-tools-dev%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/rhino-tools-dev?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en.
