November 4, 2010 at 11:58 pm
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:
November 5, 2010 at 12:41 am
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
November 5, 2010 at 8:19 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 14, 2010 at 8:16 am
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