September 13, 2011 at 8:53 am
Hi iam new to Db2 and i got a situation where i need to Update a table from another table condition ,both are on different databases
the condition is if(database1.user.col1="Outside")
Update database2.customer.col2
Set database2.customer.col2="something"
else
Set database2.customer.col2="somesome"
Any help can be greatly appreciated
September 13, 2011 at 9:25 am
This is an SQL Server site, so I'm not sure advice about DB2 will be fully accurate.
Are you using SSIS to do the update? (Asking because that's the specific forum.) Or are you using something comparable to a linked server, or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 13, 2011 at 9:29 am
yes iam using ssis in order to do the update
September 13, 2011 at 9:31 am
How far have you gotten in SSIS? Connections established and tested? Dataset from the source table created?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 13, 2011 at 9:53 am
yes i have built the connection managers for both source and destination and i have used IBM OLEDB Provider for DB2 and where should i specify the condition
September 14, 2011 at 7:47 am
That sort of operation is kinda ugly in SSIS, because ideally SSIS is supposed to be used as an ETL operation - in other words, take a set of data, perform some manipulations on it, and then put that data back.
What you're trying to do is take a set of data, and then change it, which SSIS isn't really designed for.
It's still doable though.
What you want is a Data Flow Task, which contains a OLE DB Source, pointing to the table Database1.User. Grab all of the data from it, through either a SQL Query or just choosing the table and selecting the required columns.
Then connect that to a Conditional Split object, with the split condition Col1 == "outside". Connect the true and false cases to two seperate OLE DB Command objects. Each OLE DB Command will have the script "UPDATE database2.customer.col2 SET database2.customer.col2= 'something' WHERE database2.customer.ID = ?". The "?" is important, because that's the parameter that will let you identify the row to be updated. I assume that it is an ID field, and the ID from Database1.customer will be the ID you need.
September 14, 2011 at 9:11 am
Perfect thank you very much,i can perform it using a query but my manager wants to do it through ssis ,anyway thanks for the solution
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply