[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Issues with Insert and Delete in SSIS 2008 Package

I have a situation where I have 2 tables
1) Orders (OrderID, OrderNo, Name, Invoice, OrderStatus) -- OrderID is an Identity Column
2) OrderDetails (DetailID, OrderID,ItemName, ItemDesc, Weight, Volume, Quantity,ItemStatus) -- DetailID is an Identity Column

Orders table will have all the Orders and  OrderDetails table will have all the details for the Order.

My Source table will have all the Orders and the order details. Now I have to
Insert/Update Orders and OrderDetails into both the tables.

I am able to Insert\Update Orders and this is fine.

Problem: For Order Details,
1)I have to find wether the OrderID Exists in OrderDetails Table, If it dosent exist I have to Insert the Order and its details into the OrderDetails table and set the Detail Status to "New"

2) If the Order Exists in the OrderDetails Table, I have to check what detail attributes have changed (An Item Might have deleted or Item Code Chenges or Weight, Volume etc may Change).

a) If there is no change in the Order Attributes, There is no need to change any thing on the OrderDetails Table.
b) If there is a change on the Details for the order Coming in from the source like (An Item Might have deleted or Item Code Chenges or Weight, Volume etc may Change) then I have to Delete all the Existing records from the OrderDetails table for that OrderID and then Insert the OrderDetails for that OrderID into the OrderDetails table with ItemStatus "UP" for all the Details for that Order.

I have a problem with this 2(b) part. Please guide me with this... Thanks In Advance
0
karon1980
Asked:
karon1980
  • 3
  • 2
1 Solution
 
sachitjainCommented:

For each order I could see your subset of collection as ItemName, ItemDesc, Weight, Volume, Quantity. There is no itemcode here so you can't predict if itemcode has changed. If you want to do so, you also need to include itemcode in OrderDetails table. Other thing I am assuming that change could be in any of the following 3 forms for any pre-existing orderid in orderdetails table.
1.> ItemDesc, Weight, Volume and Quantity are the attributes, either of which have changed for any item that is already pre-existing in orderdetails table for given order id.
2.> Either of pre-existing item got deleted from orderdetails for given orderid.
3.> New item added in orderdetails for given orderid.

Is that correct? If YES then I believe you want to delete records for all items completely from orderdetails table for given orderid and insert new records for all items with itemstatus as 'UP'.

So make it a 3 steps check.
1.> Check count of items for given orderid in both source and destination, if it does not match then right away delete records for all items completely from orderdetails table for given orderid and insert new records for all items with itemstatus as 'UP'.
2.> If record counts match in step 1 then compare distinct item names (or itemid if include them instead) for given  order Ids between both source and destination. If they do not match then delete records for all items completely from orderdetails table for given orderid and insert new records for all items with itemstatus as 'UP'.
3.> If distinct item names (or itemids)  are also matching then last possibility left is just of an attribute change so you need to drill down and compare each attribute for given item under given orderid in destination from the same in source.

You can't do all these things thorough single query, you need to loop around orderdetails table for each distinct orderid to perform these checks and consequent actions.
0
 
karon1980Author Commented:
Thanks SachitJain, This is what exactly what I want, Yes I have ItemCode in my table and some how missed to mention it here. To tell you frankly I am not that good at Coding, thats why I thought of doing it in SSIS. But I am facing problems with Lookup, Since each Order will have multiple Items the lookup is just looking up at top 1 row for each orderID in Details table. Thanks Once again..
0
 
sachitjainCommented:
I believe this could be easily done through a stored procedure. If you really want to use SSIS, you just need to call that stored proc through SSIS. Between are your source and dbs lying in different SQLServer instances?
0
 
karon1980Author Commented:
My Source is a staging table which will contain both orders and their details and my target tables are Order and OrderDetails. All 3 of them are on the same server.
0
 
sachitjainCommented:
Then stored procedure should be enough to do this job. I have already mentioned the outlines to be followed. Still if you find difficulty in writing any particular query, revert back here.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now