I got two tables from 2 different server A and B.
I do a OLEDB source [server A] with "select ID, currencyNo, exchangerate from table A"
I do another OLEDB [ server B] source with "select currencyNo, currencyName from table B"
i want to combine these two OLEDB sources with a resultset
"select ID, currencyNo, currencyName, exchangerate from table A , B
where A.currencyNo = B.CurrencyNo"
how do i do this in SSIS? sorry if i m a noob. I dun want to use linked servers. can someone help?
Running that kind of query is not something that SSIS is going to do magically for you. You have to ask your DBA to set link servers (assuming they are SQL Server).
Other option is to use 2 OLE DB source and then use a Merge Join transformation to merge both sources in a single output.
Both solutions may impact negatively the performance; but that will be really an issue depending in other factors like volume of data and hardware configuration.
A third option is to break work in 2 pieces; first extract data from each table and load it in a common database and then once both tables are in the same database you can use a single ole db source component.
As you can see you have several options
Rafael Salas
|||Just remember that if using a Merge Join transformation, your inputs need to be sorted on the key. Also, it is best to do the sort in your source SQL if possible, and avoid using the sort transformation in SSIS.Phil|||
i got problems using the merge join....it say i need to sort the data....
|||That's what I said you needed to do in my previous post.|||Thanks, i solved my problem!!