Issues with Insert and Delete in SSIS 2008 Package

Posted on 2011-10-19
Last Modified: 2012-08-13
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
Question by:karon1980
    LVL 12

    Accepted Solution


    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.

    Author Comment

    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..
    LVL 12

    Expert Comment

    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?

    Author Comment

    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.
    LVL 12

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now