Hello all, I'm planning to develop a new SSIS project where I have a data in a table (Raw form) and I want to put into our production database. I just want to start with a test project and if you provide me little inside that will be helpful. Here is what I'm trying to do Typical order table (Order Header and Order Detail) Temporary table with raw data which need to import in Order Table. Order Header contains auto increment primary key which needs to be in order details table. So how should I develop a project where I go thru each record of temp table, write a record in order table, get order id (pk) and write data in order detail table. Hope it is very much clear. Hopefully I will get expert advice soon. Thanks in advance, P mark it as answer if it answered your question :)

The simplest way to implement this is to do 2 passes through your temp table. On the first pass (using a data flow), create your header records. In the second pass (a second data flow), use a Lookup component to retrieve the Header id, and create your detail row. There are other ways to handle it, but generally, they perform more slowly than just making two passes, plus they make the package a lot more complex.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

if i run two pass, how i will associate correct order id which i will get in 1st pass with detail table, is it possible then when i run 1st pass, i get order id then at the same time i write the line item order detail.mark it as answer if it answered your question :)

FYI - If you are new to SSIS, you'll need to spend some time learning it before any of this will make much sense. In the data flow for your second pass, you can use a lookup component to retrieve the autogenerated ID. You need a way to match the order to the newly created record - I'm assuming the temp table has enough information to uniquely identify an order (like an Order Number, or some combination of values like the Order Date and Customer Number). Then you insert a new Order Detail row, passing along the ID you retrieved from the lookup. If you really want to do it in a single pass (and I don't recommend it - you will get poor performance and you won't be using SSIS to it's full potential), you can use an Execute SQL to retrieve the rows from the temp table. Then use a For Each Loop to iterate through the rows (this post shows the basics of setting this up: http://agilebi.com/jwelch/2007/03/21/using-for-each-to-iterate-a-resultset/). Inside the For Each loop, you need to add an Execute SQL to insert the header row. You can use the T-SQL OUTPUT clause (explained here: http://msdn.microsoft.com/en-us/library/ms177564.aspx) to get the ID back. Add a second Execute SQL to perform the Item insert. You can pass the ID between the two tasks using a package variable.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

so here is what i understood. 1. move all rows from temp table -> order header. and during this move, update orderid field in temp table from auto generated id number in order header. 2. use temp table again, dump data in order detail table and use order id from temp table. correct? thanks for your help thoumark it as answer if it answered your question :)

Not really, no :) I can put together a sample /walkthrough of this, but I need a little more info. What columns do you have in your temp table? Sample data would help too - just don't include any real data. Second, are you familiar with data flow tasks at all? Not saying that you have to be, but it helps me know how much I need to explain.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

First, thanks for taking time out and doing it. INfect, i did small import export data movement with wizard and not thru projects. Whatever way you wnt to explain that is fine, i will get back if need more info. here are the tables :- temp table email, customer_id,product_id, order_id abc@abc.com,1,1,null xyz@xyz.com,2,2,null aaa@aaa.com,1,3,null order header table id - pk , identity date - default, current date customer_id - from temp table order detail table id - pk, identity order_id - fk, refer to header table. product_id - from temp table let me know if need more informationmark it as answer if it answered your question :)

I put together a quick sample (based on your tables) that uses two dataflows to do this. http://cid-71c6f14e3c205217.office.live.com/self.aspx/Public/Patterns%20For%20Parent%20Child%20Processing/LoadParentChild^_TwoDataFlows.zip I'll put a walkthrough on my blog tonight, but wanted to get this to you as the walkthrough may take a while.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

hey John, I gone thru your project and able to understand it. Thanks a lot. Only area I need to work on is that parent table (in real data) I have already records in existing table so I have to filter the records only where records are added from temp table to match on customer id. I think this process is ok but challenge i see if there one customer has more than one rows in temp table, it will fail because your look up is working on customer id . need to refine little more. that's why i was planning to do is, write data in parent table as per your example and during this update order number in temp table and then in 2nd pass, write data in child table and then there is no need to lookup at order table to get the order id and it will work if one customer has more than one order. I know you mentioned i need to use for each loop which will slow but i think it will be more fail proof. do you think you can put an example with for each loop, write data in parent and then update order id in temp table? let me know what you think? thanks, Pmark it as answer if it answered your question :)

One workaround - add date to the lookup, so that it matches on both the customer id and the current date. From the sample data you provided, I wasn't sure how you were planning to handle it when there are two line items for the same customer on the same day, but I guessed that you'd want to combine it to a single order. If you make that change, it should work fine. I was planning to put the other example together in conjunction with the blog post (basically for completeness, even though I still am not very found of that approach) but I won't have time till late this evening.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

first of all there are no two line item, one line in temp table is one order. but there are possibilities that same customer order two different things in that case two different order. also date approach will not work sorry for all this but i'm more into fool proof solution but anyhow you project given me good understanding. look forward to get another example on for each loop thanks mark it as answer if it answered your question :)

Sorry about the delay on this - I was travelling earlier this week and have just cuaght up with everything. Here's a sample using the For Each loop: http://cid-71c6f14e3c205217.office.live.com/self.aspx/Public/Patterns%20For%20Parent%20Child%20Processing/LoadParentChild^_TwoDataFlows.zipJohn Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com