SSIS package for Normalized tables

  • I have a requirement, where i need to upload a report in SQL server 2005 table.

    For example:

    I am having a report for Order Details Query where fields are

    OrderId, ProductName, ProductPrice, UnitPrice, Quantity, Discount.

    Design:

    [Order Details]

    OrderId int identity(1,1)

    ProductId int

    UnitPrice numeric(10,2)

    Quantity int

    Discount numeric(10,2)

    [Products]

    ProductId int identity(1,1)

    ProductName varchar(100)

    Now Product Name will go to [Products] table.

    OrderId, ProductPrice, UnitPrice, Quantity and Discount will go to [Order Details] table.

    [Products] and [Order Details] are having a common field of ProductId

    When we upload a report using SSIS package, it should work as described below.

    Step1: Check if Product Exists in [Products] table.

    Step 2: IF product exists, get the ProductId ELSE insert the ProductName and get the ProductID.

    Step 3: Insert the OrderId, ProductPrice, UnitPrice, Quantity, Discount and ProductId from Step2 to [Order Details] table.

    How the package should be designed. Please help me out!

    Hiren Shah

    :kiss:KISS (Keep It Simple Stupid) :kiss:

  • Please explain what you mean by 'report'?

    From what you are describing, it just sounds like a straight INSERT.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • You could do it like this:

    Source -> Lookup Component to check if product exists -> Yes then pass row through with the ProductID added to the flow -> Destination - Order Details.

    A the product lookup component you can pass through error rows (no product found) to a product destination table to create the product -> to a product lookup to get the id -> to another Destination for order details.

    You could also allow all the rows through the Product Lookup and then use a conditional split instead of the error data flow.

  • A the product lookup component you can pass through error rows (no product found) to a product destination table to create the product -> to a product lookup to get the id -> to another Destination for order details.

    As per the suggestion above, How can I achive the same.

    From the Lookup Transformation , I get error rows. Now if take a SQL destination, then i cant have the output path to go again for a product lookup???

    I am confused, please guide

    Hiren Shah

    :kiss:KISS (Keep It Simple Stupid) :kiss:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply