How to load multiple rows of order data with same info on each row for order but only product info is different?
Hi, I am newbee to SSIS so i hope the experts go easy on my lame question. I am getting my order information in an excel file in the following format and order and i need to load these into orderheader (OrderNumber, CustomerID), customer(customerid, customerFirstname, customerlastname) and orderDetail (product infomation) tables. In C# i used to load this by reading the data one row at a time and checking for ordernumber and loop through rest of the info if that remained the same as pervious. Now i am trying to move to SSIS and was wondering if there's a better approach that i can use or should i be using script control. Any guideance would be greatly appreciated. Thanks. OrderNumber CustomerID CustomerFirstName CustomerLastName Product ID Model No Serial No Date of Purchase ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 000001 A1 John Doe 111 1x1x1x 1s1s1s 11/1/2012 000001 A1 John Doe 112 1x1x2x 1s1s2s 11/1/2012 000001 A1 John Doe 113 1x1x3x 1s1s3s 11/1/2012 000001 A1 John Doe 114 1x1x4x 1s1s4s 11/1/2012 000001 A1 John Doe 115 1x1x5x 1s1s5s 11/1/2012 000001 A1 John Doe 116 1x1x6x 1s1s6s 11/1/2012 000002 A2 John1 Doe1 111 1x1x1x 1s1s1s 11/1/2012 000002 A2 John1 Doe1 112 1x1x2x 1s1s2s 11/1/2012 000002 A2 John1 Doe1 113 1x1x3x 1s1s3s 11/1/2012 000002 A2 John1 Doe1 114 1x1x4x 1s1s4s 11/1/2012 000002 A2 John1 Doe1 115 1x1x5x 1s1s5s 11/1/2012 000002 A2 John1 Doe1 116 1x1x6x 1s1s6s 11/1/2012
November 9th, 2012 1:57pm

Hello, SSIS will definitely provide you with multiple options to load your result set into the destination tables. A Excel Source Data Flow Should be Added Next which reads your source Excel and is ready with your result set. You can choose to redirect your source into multiple destination objects. If your number of records or not very high use a Lookup Transformation that will help you isolate records that are to be inserted and records that are to be updated. An Ole Db Destination with the Fast Load Option will help you insert data (that does not match your lookup condition). An OLE DB Command with a update SQL will take care of the updates. It's always better to catch your bad records(the red line in ssis). We have a pattern where another OLE DB Destination with slow load Object tries to save the errors from the batches during the fast load. You can redirect the error logs to a Flat file, or another DB table if you want. I would also recommend that you split the task in 3 simple data flow transformation for ease of maintenance and debugging. If the records are high, and the package is not expected to run multiple times, you should dump the resultset onto a Staging Table, after clearing it each time and then use Merge SQL inside a Execute SQL Task to insert/update records in the corresponding tables. Regards, Dinesh
Free Windows Admin Tool Kit Click here and download it now
November 9th, 2012 2:35pm

Thanks for the input Dinesh, but what i am looking for is the logic and steps needed to process these multiple rows with same data (only difference is the product information) so that there is only one entry in orderheader table and customer table but multiple entries in the product table. How do i achieve that in SSIS. I apologize if you answered that in the above post, if you did i didn't actually get it how to do that. Thanks.
November 9th, 2012 2:41pm

I Apologize if i was not clear earlier For e.g. I Defined a Excel similar to your screenshot. I created a new SSIS Package Set up a Excel Connection manager to point to the Excel File. I added a Data Flow Task, a Excel Source and in the Editor i Chose SQl Command , in the Text Window i entered SELECT Distinct ORDERID, CUSTOMERID FROM [Sheet1$] In this case it would list only the distinct combination of Order and Customer Information. Use a Lookup Transfromation and OLE DB Destination to Save the Data The URL will show how to use Lookup and save transformation SSIS Lookup Transformation use 4. Add one more Data Flow task in the Control Flow, Add a new Excel Source this time add the query to list down the Customer Information SELECT DISTINCT CustomerID, FirstName, LastName FROM [Sheet1$] Repeat the same process for each table you want to populateRegards, Dinesh
Free Windows Admin Tool Kit Click here and download it now
November 9th, 2012 5:08pm

I Apologize if i was not clear earlier For e.g. I Defined a Excel similar to your screenshot. I created a new SSIS Package Set up a Excel Connection manager to point to the Excel File. I added a Data Flow Task, a Excel Source and in the Editor i Chose SQl Command , in the Text Window i entered SELECT Distinct ORDERID, CUSTOMERID FROM [Sheet1$] In this case it would list only the distinct combination of Order and Customer Information. Use a Lookup Transfromation and OLE DB Destination to Save the Data The URL will show how to use Lookup and save transformation SSIS Lookup Transformation use 4. Add one more Data Flow task in the Control Flow, Add a new Excel Source this time add the query to list down the Customer Information SELECT DISTINCT CustomerID, FirstName, LastName FROM [Sheet1$] Repeat the same process for each table you want to populateRegards, Dinesh
November 9th, 2012 5:08pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics