ssis combine data from two table
-
Tuesday, January 15, 2013 8:12 PM
hi,
i have two tables ,
product
productid size quantity
1 24 4
2 34 5
3 32 6
productdetail
productid name
1 phone
2 tablet
3 pen
i need to insert data into third table which is in another server
product_final
productid size quantity name
1 24 4 phone
how to do using ssis.i dont need to do using execute sql task, as the third table is in another server
any other idea
All Replies;
-
Tuesday, January 15, 2013 8:17 PMModerator
Merge Join Transformation would do the trick here
You most probably need sort the input, so here is a more detailed blog article on how to do all: http://www.sqlhub.com/2010/08/merge-join-transformation-task-in-ssis.html
Arthur My Blog
- Edited by ArthurZMVP, Moderator Tuesday, January 15, 2013 8:19 PM
- Proposed As Answer by SSISJoostMicrosoft Community Contributor, Moderator Friday, January 18, 2013 8:59 AM
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Thursday, January 24, 2013 3:29 AM
-
Tuesday, January 15, 2013 11:43 PM
I don't think a merge is actually needed since it sounds like the first 2 tables are on the same server and the destination is on another server.
A dataflow with a simple SQL join in the data source would do and then you would write to your 3rd table as a destination
Your datasource would just be:
SELECT p.productID ,p.size ,p.quantity ,pd.name FROM product p JOIN productdetail pd ON p.productid = pd.productid
And then you would point your destination in the dataflow to product_final on your other server/database.
Chuck Pedretti | Magenic – North Region | magenic.com
- Edited by Chuck Pedretti Tuesday, January 15, 2013 11:46 PM
- Proposed As Answer by SSISJoostMicrosoft Community Contributor, Moderator Friday, January 18, 2013 8:59 AM
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Thursday, January 24, 2013 3:29 AM
-
Friday, January 18, 2013 2:13 AM
Or if you really dont like to use any TSQL scripting, you can use the Lookup Transformation.
- Proposed As Answer by SSISJoostMicrosoft Community Contributor, Moderator Friday, January 18, 2013 8:59 AM
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Thursday, January 24, 2013 3:29 AM
-
Friday, January 18, 2013 8:58 AMModerator
You have three solutions:
1) Merge join, but try to do the sorting (which is needed for the merge join transformation) in the source query. Sort is a blocking component and that could be slow if there is a lot of data. Don't forget to set the isSorted property on true in the advanced source editor. More info
2) Join in source query. Since the tables are on the same server this would be even faster.
3) The Lookup, same result and also realy fast.
Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
- Edited by SSISJoostMicrosoft Community Contributor, Moderator Friday, January 18, 2013 8:59 AM
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Thursday, January 24, 2013 3:29 AM
.png)
