December 29, 2008 at 7:03 am
Hi,
I am trying to insert/update on the basis of if a new Project_id exist in the source file or update if there is existing Ids.
The package executes fine if we don't have multiple project ids.
But in case we have multiple project ids, how can I update the table.
I am using lookup control and conditional split control to identify if the project ids from the source file is a new one or the existing one.
December 30, 2008 at 12:14 am
You actually have 3 options to do this:
2. Use the lookup and conditional split to insert your updated rows to a staging table, then as your next step in the package do an execute SQL task that does a set-based update.
3. Just put all the data in a staging table and use an Execute SQL Task that does a set-based insert and a set-based update.
I would do #3 if I were you as it will be the fastest. Basically I would have 2 Execute SQL Tasks and 1 data flow in the package. The first Execute SQL Task would truncate the staging table, then the Dataflow task would re-load the staging table, and then the last Execute SQL Task would call a stored procedure that has 2 steps:
Update table
Set Columns = S.columns
FRom
Table T Join
Staging S On
T.id = S.id
Insert Into table
(
columns
)
Select
S.columns
From
staging S Left Join
table T On
S.id = T.id
Where
T.id is null
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
January 8, 2009 at 9:38 pm
Unfortunatelly your suugesstion did not work for me. I will try to put my question in more detail.
Lets say I have to import an excel file data into a table t1. Excel file data contains two column as:-
Project_Id Product
1 A
2 B
2 C
1 D
I have to import the above data into table t1. If new Project_id found then I need to insert the row or else I have to update. I am trying to accomplish the above task using a SSIS.I am using lookup and conditional split component to identify if there is new Project_id(to be inserted)or an existing Project_id(to be updated) in the excel source.
Had there been a unique Project_Id then the task would have been easily accomplised but in this case there are multiple Project_Id. How to update in this particular case. Please help.
January 8, 2009 at 10:44 pm
In your example, what do you want the final result to be in t1? Do you want 1 row for each project id? Then you need to decide which product you want, min or max?
In order to update you need something unique to match on.
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
January 8, 2009 at 11:11 pm
Thanks for your quick response.
The final result in t1 should be exactly same as in source file.
If in the source file data is :
Project_Id Product
1 A
2 B
2 C
1 D
then in table t1 the same data should be populated as :
Project_Id Product
1 A
2 B
2 C
1 D
If some is updated in the source file as :
Project_Id Product
1 A
2 B
2 E
1 D
Then in t1 the updated should be reflected:
Project_Id Product
1 A
2 B
2 E
1 D
The problem is, my SSIS package is not updating it correctly. What should be the actual sql query written in OLEDB command component.
January 9, 2009 at 10:43 am
I'm a bit confused by your last example. Your update does not show anything being updated. It sounds to me like your business key is Project_ID, Product and you'll be updateing other columns within that table?
You may try using the Slowly Changing Dimension task. Set it up so Project_ID, Product are your business key, and set the other columns as changing attributes.
January 9, 2009 at 10:54 am
If all you want is a copy of the source in the destination the simplest and most error proof solution is to truncate and reload everything.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply