Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
notes4we
Yak Posting Veteran
90 Posts |
 Posted - 2008-11-20 : 11:50:13
|
| I have a scenario for which I want to know the best possible way to extract, transform and load the data.There are two servers and two sources: one is a SQL Server table and the other is an Oracle table. Now, both of these source tables have one column name named "IdNo" which is same in both the tables. I mean SQL Server table has a column named "IdNo" and even Oracle table has one column with a similar name.Now in both the different tables, the column name having "IdNo" has few "IdNo"'s that are matching, while some are not.So, what I have to do is, I have to extract the data from both these tables and then load it into an oracle destination putting the condition that only if the "IdNo" from both of these tables match, then load it into the destination. I am having trouble because of the two different data sources.I am stuck with this and I want to fix this as soon as I can.Any help would be greatly appreciated.I am not sure if I need to add a linked server or is there any other way that I can achieve this.Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-20 : 12:00:37
|
| you need a dataflow task with two OLEDB source tasks and a OLEDB destination task. connect OLEDB source tasks to sql table and oracle source table. then use a merge join task to join records based on column IdNo . remember to use inner join as you need only matching value. then connect output of merge join task to OLEDB destination which connects to oracle destination table. |
 |
|
|
notes4we
Yak Posting Veteran
90 Posts |
Posted - 2008-11-20 : 12:11:38
|
quote:
Originally posted by visakh16
you need a dataflow task with two OLEDB source tasks and a OLEDB destination task. connect OLEDB source tasks to sql table and oracle source table. then use a merge join task to join records based on column IdNo . remember to use inner join as you need only matching value. then connect output of merge join task to OLEDB destination which connects to oracle destination table.
Thank yo so much for your prompt reply. I will need to sort the data first, in order to use the merge join task, right? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-20 : 12:14:21
|
yup. you need to. just use select fields from table order by IDno as source query for OLEDB source tasks after selecting sql command option. |
|
|
|
notes4we
Yak Posting Veteran
90 Posts |
Posted - 2008-11-20 : 13:09:59
|
| Hey Visakh, what you said is working fine.But there is one more step that I need to do.As I said there is a column named "IDNo" in both the tables (SQL and Oracle), I am able to join the data using a merge join (inner join), but in the Oracle destination I have one more column named 'change' and I want it to be updated.It means if there is any matching row "IdNo" in both the tables, then in the destination the column named change for that particular IdNo should be updated using an Update Query which has UPDATE ServerName.TableNameSET change = "ABC"WHERE Table1IDNo = Table2IDNoSo, after the merge join, I tried using an OLEDB command to achieve this. Is there any other way to achieve this?Thanks you. |
|
|
|
|
|
|
|
|
Two different sources and one destination